0

I am new to Excel and I have been trying to complete some work using Excel by googling formulas. I came across sumproduct and I believe it will work for what I need but I may not be writing the formula correctly.

I need to count every nth cell in a column if it is "2" and the preceding cell in the same column is "0". From what I have seen online, it seems like a sumproduct with 2 arrays should work. This is what I came up with but does not work:

=SUMPRODUCT(((MOD(ROW(C2:C111)-ROW(C2),5)=0)+0,ISNUMBER(SEARCH("0",C2:C111))+0)*((MOD(ROW(C3:C111)-ROW(C3),5)=0)+0,ISNUMBER(SEARCH("2",C3:C111))+0))

Any assistance will be appreciated!

Lux
  • 1,540
  • 1
  • 22
  • 28
user8341106
  • 1
  • 1
    try entering the formula pressing Ctrl+Shift+Enter rather than just Enter. you could also look into keeping the logic of "IF 2... else IF 0..." in a separate helper column returning the values you want to count – airstrike Jul 20 '17 at 22:33

1 Answers1

0

=SUMPRODUCT(IF(C1:C114=0,1,0),IF(C1:C114<>"",1,0)*IF(C2:C115=2,1,0))

 OR

=SUMPRODUCT((C1:C114=0)*(C1:C114<>"")*(C2:C115=2))

and use CTRL+SHIFT+ENTER for an array formula.

NB: These are equivalent. The ranges are offset by one row to satisfy your condition that "0" is a row above "2". There is a third condition so if a blank cell is above the "2" this is ignored, otherwise the formula evaluates the NULL value as zero.

Tim Joy T-Square Consulting
  • 266
  • 2
  • 12