# How to count instances of values excluding duplicates?

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.

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:

``````=SUMPRODUCT(--(\$A\$1:\$A\$19<>OFFSET(\$A\$1:\$A\$19,1,0)),--(OFFSET(\$A\$1:\$A\$19,1,0)<>""))
``````

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.