Count consecutive occurrences when sum of two consecutive numbers below 3

Bapt75

New Member
Hello,

I have a column of numbers :

3
0
4
2
1
0
5
0
3
2
0
2

I am trying to get the number of times the sum of two consecutive rows is below 3. In this example 4 (i.e. 2+1,1+0,2+0 and 0+2).
I tried a way around with this formula :

=SUMPRODUCT(--(FREQUENCY(IF(C5:C16<2, ROW(C5:C16)), IF(C5:C16>=2, ROW(C5:C16)))>=2)) based on an old post but it is 1) not exactly what I am looking for and 2) not working anyway here for some reason I don't understand.

THANK YOU VERY MUCH FOR YOUR HELP !

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

Bapt75

New Member
My bad the title is confusing and should be "Count occurrences when sum of two consecutive rows below 3"
Thanks

steve the fish

Well-known Member
Hi. Your title doesnt match what you ask for in the post. One says below 3 then the other counts 3 as valid. Anyway this does less than 3:

=SUMPRODUCT(--(A1:A12+A2:A13<3),--(A1:A12<>""),--(A2:A13<>""))

Bapt75

New Member
Hi. Your title doesnt match what you ask for in the post. One says below 3 then the other counts 3 as valid. Anyway this does less than 3:

=SUMPRODUCT(--(A1:A12+A2:A13<3),--(A1:A12<>""),--(A2:A13<>""))
Thank you very much it works !!

You are right I made a mistake in my example, the result should be 3 not 4 (correction for people reading the thread in the future).

1) Do you know why it is not possible to drag the formula across columns?
2) Could you explain how the second/third arrays work?

Many thanks

steve the fish

Well-known Member
It is possible to drag it across columns. Excel will see a blank cell as zero so they are just testing to see if the cell really is zero or if its blank. Blank doesnt count but zero does.