Counting 0's

[E61]

Hi All,

New to the forum, looking to count the 'total' number of 0's in a range.

Have a number range from 1 - 107, within this range there are 18 instances of 0, I have tried the COUNTIF and a wildcard but cannot get the result of 18 instances of 0 that are present within this range.

If you are all wondering what a strange request.....this was based on a problem I had where someone asked me how many 0 stickers will I need for the range from 1 - 107 (each number is a separate item), I quickly added up in my head and arrived at 17 forgetting 100 has 2 zeros!! So I messed up and just thought if the question ever surfaced again I could do a double check!

Thanks - Ian

[TheCman81]

It should just be

=COUNTIF(A1:A107,0)

[E61]

This is what I thought but returns 0

[Special-K]

This is what I thought but returns 0

Then they're not zero.

Find one that you think is 0 let's say A14
In another cell, any cell enter =(A14=0)
If it says TRUE its a zero, if it says FALSE its something else (could be spaces, non ASCII characters in the same cell).

[E61]

Maybe I havent explained myself properly..... i.e 100 contains 2 0's

[E61]

It states false for A10 which is 10 therefore contains one 0

[Special-K]

=COUNTIF(A1:A107,0)

will count zeroes from A1:A107

Not sure why you are using a wildcard.

[E61]

Literally just auto filled from 1 - 107

[Special-K]

=SUM(LEN(A1:A107)-LEN(SUBSTITUTE(A1:A107,"0","")))

Its an array formula, use Ctrl-Shift-Enter.

Source: http://support.microsoft.com/kb/213889

[E61]

Legend!! You know your stuff