This is what I have for my D2 cell:
=COUNTIF(B1:B50="yes",=SUM(A2)-1),IF(OR(C1:C50,"yes",=SUM(A2)+1))
Basically what I'm trying to accomplish is this.
- I have
=SUM(A2)
in my E2 cell for it's formula - I have a total amount in A2 say 500
- I will have "yes" in some cells ranging from B2 to B50
- I will have "yes" in some cells ranging from C2 to C50
- If there is a "yes" in any cell range from B2 to B50 I want it to subtract 1 from E2 and show the new total. I.E. total was 500 I type yes and tab or press enter then E2 will now show 499
- Same as #4 except if there is a yes in column C2 to C50 I want it to add one.
I have done this before, but it's been a while and all I can remember are two pieces and those are =COUNTIF(B1:B50,"YES")
and =SUM(E2)-1
and I vaguely remember having one or two hidden cells to help make it work.
Thank You in Advance I appreciate any help on this.