0

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.

  1. I have =SUM(A2) in my E2 cell for it's formula
  2. I have a total amount in A2 say 500
  3. I will have "yes" in some cells ranging from B2 to B50
  4. I will have "yes" in some cells ranging from C2 to C50
  5. 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
  6. 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.

1 Answer 1

0

How about something like this?

=A2-COUNTIF(B1:B50,"yes")+COUNTIF(C1:C50,"yes")

This should work if you want to subtract 1 from the figure in A2 for each "yes" in the B column and add 1 to the figure in A2 for each "yes" in the C column.

If you just want to subtract 1 or add 1 if there is a "yes" in either column, this should be the kind of formula you're looking for:

=IF(AND(COUNTIF(B1:B50,"yes")>0,COUNTIF(C1:C50,"yes")=0),A2-1,IF(AND(COUNTIF(B1:B50,"yes")=0,COUNTIF(C1:C50,"yes")>0),A2+1,A2))

What this one does is subtracts 1 if a yes is in the first column, adds 1 if it's in the second column, and just reports the number in A2 (500 in your example) if both columns have a yes or are both empty.

3
  • why do you have SUMs in your first formula? A2-COUNTIF(B1:B50,"yes")+COUNTIF(C1:C50,"yes") would also work correctly. Nov 20, 2015 at 6:33
  • @MátéJuhász You're right. I started with the sum formula in the original question and didn't notice it was superfluous in the formula I ended up with. Edited.
    – CSmith
    Nov 20, 2015 at 6:41
  • Thank you for the formulas, I will definitely try them as soon as I get back to work. The SUMs in the one I added are there, as the examples I was looking at all had that in them either at the beginning or the middle. I thought there needed to be a SUM to tell it ok, write the answer if that yes statement is true.
    – user524181
    Nov 20, 2015 at 10:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .