I have a column of values that are either 5 or 0. I am trying to count the instances that the number 0 appears, while counting the zeroes that appear more than once consecutively as only one instance.

enter image description here

For example, in the above image, the 0 that appears in A6 would count as one instance, but the two 0s that appear in A10 and A11 would also only count as one instance as they are consecutive. How would this be achieved?


OK so I have had a go of this, but don't quite understand fully what my formula is doing so cannot really explain it well,

But it seems to work:


Let me know if it works for you on a larger data set. (if not I will remove answer)


I'm working with german Excel, so i can't paste my fomula here as it has commands in german language. But that's how i'd solve this, based on the image you provided.

1) Handle the difference between the first value (A1) and all other Values. For the very first value, there is nothing before it. So in B1 i'd write something like:

=IF(A0=0;"counts";"doesn't count")

2) For every following value, you can check the previous ones. So in B2 i'd write a formula like:

=IF(AND(A2=0;A1=0);"doesn't count";IF(A2=0;"counts";"doesn't count"))

3) Drag this formula from B2 over all your data fields below. Results in this:

    A   B
1   0   counts
2   0   doesn't count 
3   5   doesn't count
4   5   doesn't count
5   5   doesn't count
6   0   counts
7   5   doesn't count
8   5   doesn't count
9   5   doesn't count
10  0   counts
11  0   doesn't count 
12  5   doesn't count
13  0   counts
14  5   doesn't count
15  5   doesn't count
16  5   doesn't count
17  0   counts
18  0   doesn't count 

...and you can handle all that counts as you like, and... well... count them.


You can use SUMPRODUCT this way:

=SUMPRODUCT((A1:A18 = 0)*(A2:A19<>0))

Which basically counts whenever 0 changes to something else.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.