# Thread: Count consecutive occurrences when sum of two consecutive numbers below 3 Thanks: 0 Likes:  1 Post #5297094 (1)

1. ## Count consecutive occurrences when sum of two consecutive numbers below 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 !  Reply With Quote

2. ## Re: Count consecutive occurrences when sum of two consecutive numbers below 3

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

3. ## Re: Count consecutive occurrences when sum of two consecutive numbers below 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<>""))  Reply With Quote

4. ## Re: Count consecutive occurrences when sum of two consecutive numbers below 3 Originally Posted by 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<>""))
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  Reply With Quote

5. ## Re: Count consecutive occurrences when sum of two consecutive numbers below 3

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.  Reply With Quote

## User Tag List

consecutive, excel, numbers, post, sum 