Thread: If and Countif treat blanks differently?

Hi,
I am learning to use Excel 2007. I get different results using IF() and Countif() in a situation where I expect them to behave the same. Suppose I have a sheet called "green status pivot" It contains a pivot table. The pivot table contains some numbers (each number is either a "0" or a "1") and some blanks (empty cells in the pivot table because the underlying data has missing values). I want to count the number of cells with value "0" (as opposed to value "1" or missing value).

Suppose I have an empty cell in a cell O20 in that sheet with the pivot table. If I do =COUNTIF('green status pivot'!O20, " = 0") I get 0, but if I do = IF('green status pivot'!O3 =0, 1,0) I get 1. Is this supposed to happen (if so, is there some underlying logic?) or am I messing up something? Is there some way to specify to Excel how to treat blanks (missing values) in IF() and in Countif()?

Many thanks.
Studiosa

How about =COUNTIF('green status pivot'!O20, " 0")

Hi,
Thank you very much for your reply.
Actually countif(range,0") does exactly the same as countif(range, "=0")


Studiosa.

This counts blanks:

=COUNTIF(A1:A10,"=")

this counts non-blanks:

=COUNTIF(A1:A10,"<>")

(where the extra "" is redundant), and this counts zeros:

=COUNTIF(A1:A10,"0")

(where the = is implied).

Hope this helps.

Pete

Pete,
This was very helpful, thank you. I also discovered a command specifically for counting blanks: =COUNTBLANK(A1:A10)

studiosa