Count consecutive occurrences when sum of two consecutive numbers below 3

Bapt75

New Member
Joined
Jun 20, 2019
Messages
3
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 !
 

Some videos you may like

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
Joined
Jun 20, 2019
Messages
3
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
Joined
Oct 20, 2009
Messages
7,944
Office Version
365
Platform
Windows
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
Joined
Jun 20, 2019
Messages
3
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,944
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,469
Messages
5,414,716
Members
403,543
Latest member
alig88

This Week's Hot Topics

Top