Count consecutive occurrences when sum of two consecutive numbers below 3

[Bapt75]

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 !

 

[Bapt75]

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

 

[steve the fish]

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]

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).

If I may further ask

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]

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.