Counting Number of Consecutive Occurrences of Specific Value

[MrJones378]

Okay so I think I've exhausted my googling/foruming options trying to figure this out. I've found a ton of things that are very close to this problem but not quite the same. I'm pretty certain I'm going to need an array formula. Essentially I have data that looks like this:

[TABLE="width: 200"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Numb[/TD]
[/TR]
[TR]
[TD]1/1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1/3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1/4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/5
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1/6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1/7[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Now the data set I'm using is a good amount larger but not enormous (approx. 160 rows). What I need is to count the number of times "0" occurs consecutively. In this case, 0 would have occurred consecutively 3 times, on the 2nd and 3rd, on the 5th and 6th, and the 6th and 7th. I have another cell (lets say D4) that needs a formula that will count the number of times a 0 occurred on consecutive days.

Many thanks in advance for any insight.

 

[Domenic]

Try...

=SUMPRODUCT(--(B2:B7=0),--(B3:B8=0))

Note that the range for the second criteria is offset from the range for the first criteria by one row. Also, empty cells, if any, will be considered as having zeros.

Hope this helps!

 

[J.Ty.]

Hi,

I assume below that you have one value for each day (no jumps, no duplicates).
Under these circumstances, and assuming that you data occupies A1:B7, the formula is

=SUMPRODUCT((B2:B7=B1:B6)*(B2:B7=0))

J.Ty.

 

[MrJones378]

Hi,

I assume below that you have one value for each day (no jumps, no duplicates).
Under these circumstances, and assuming that you data occupies A1:B7, the formula is

=SUMPRODUCT((B2:B7=B1:B6)*(B2:B7=0))

J.Ty.

J.Ty wow thank you so much. I'm not exactly an excel newbie (although it may seem that way at the moment) but this was way simpler than I thought it would be. Any chance you wouldn't mind explaining to me why that function works the way it does? I guess I don't use SUMPRODUCT very often, perhaps I'm missing out.

 

[J.Ty.]

OK.

SUMPRODUCT, in the form I have used it, is indeed an alias for an array formula. The benefit is that you do not have to use Ctrl-Shift-Enter (and you cannot foget it).
My formula is thus equivalent to

[COLOR=#333333]{=SUM((B2:B7=B1:B6)*(B2:B7=0))}[/COLOR]

The form used by Domenic

[COLOR=#333333]=SUMPRODUCT(--(B2:B7=0),--(B3:B8=0))[/COLOR]

omits multiplication, which is performed by SUMPRODUCT itself (which multiplies row-by-row all ranges it gest as arguments), but needs --, which enforce conversion from logical values produced by equality tests to numbers. In my case multiplication does it - as would do any other arithmetical opertion.

J.Ty.