# Formula to count continuous instances

#### pdchislett

##### New Member
Hi,

I have a spreadsheet containing data for 52 weeks.

So something like.. (in columns A & B)

Wk1 1000
Wk2 2000
Wk3 0
Wk4 0
Wk5 0
Wk6 1000
Wk7 0
Wk8 0
Wk9 3000

Is there a formula I can use to count the maximum number of continuous zeroes? So in the above the answer would be 3 (the range between wk3-5)

Thanks

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Assuming that A2:B10 contains the data, try...

=MAX(FREQUENCY(IF(B2:B10=0,ROW(B2:B10)),IF(B2:B10<>0,ROW(B2:B10))))

Note, however, if the data contains empty cells and you'd like them to be ignored, the formula will need to be amended.

Hi Domenic,

I manipulated the formula (to the correct range in my spreadsheet) and got a #VALUE!.

I also replicated the example i gave and still got the same result?

There arent any blank cells in the data..

Thanks

Sorry, the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.

Excellent - Thanks

You're very welcome!

You're very welcome!

Hi Domenic, I have almost same problem, here is my query:

 5.5 8 7.5 6 5.5 7.5 4.5

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
This data ranged from A1:A18. In between there are blank cells but contained formula. I want to calculate how many times this series has more equal to or more than 5 consecutive entries by formula. Such as in this case there is only one range from A7:A11 (5 consecutive numbers).

Try...

=SUM(IF(FREQUENCY(IF(ISNUMBER(A1:A18),ROW(A1:A18)),IF(1-ISNUMBER(A1:A18),ROW(A1:A18)))>=5,1))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Thanks Domenic, it worked!

suppose A1:A18, every cell in this range has some value then answer should come 3 because data has 5 consecutive values three times or
in other scenario range from A1:A6 has values and then A7 blank having formula in it and from A8:A14 has values, then answer should come 2 because there are twice 5 consecutive values.

You're very welcome. I'm on my way out, though, so I would suggest that you start a new thread and ask you question there.

Replies
4
Views
173
Replies
3
Views
432
Replies
2
Views
276
Replies
1
Views
317
Replies
5
Views
357

1,203,250
Messages
6,054,383
Members
444,721
Latest member
BAFRA77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back