easy countif formula again

[Guest]

I forgot to add the following information to my last post...

Cells B1:b50 have the letter P or are blank. I would like B51 to count the
number of P's in cells B1:b50. The font in Cells B1:b50 are formated with
wingdings 3 which produces an upward arrow if the cell value is P. If I use
the formula
=countif(b1:b50,P) in cell B51, it appears that the formula will not count
because of the font formating. Can anyone help me with this? Also, could you
please provide a formula to count cells that are blank. Thank you.

 

[Dave Peterson]

=countif(b1:b50,"p")

or
=COUNTIF(B1:B50,"<>")
will count the non-blank cells.

or
=counta(b1:b50)
will count the cells that have something in them (even formulas that evaluate to
"".

 

[Myrna Larson]

I can't reproduce your problem. I gather you mean p rather than P, because the
latter doesn't look like an upward pointing arrow.

COUNTIF looks at the value in the cell (the P). Formatting has no effect on
the results.

I put this formula in A1 and copied down through A191, then formatted with the
WingDings 3 font. The formula

=COUNTIF(A1:A191,"P") gives 2

because it's not case-sensitive, and it is counting both P and p. If I put the
same formula in B1:B2, and format as Arial, COUNTIF on this column also gives
2.

 

[Dave Peterson]

=COUNTIF(B1:B50,"")
will count the blank cells

as will
=countblank(b1:b50)

 

[Alan Beban]

=COUNTIF(B1:B50,"")
will count the blank cells

This highlights the problem of not being precise in distinguishing
between cells containing empty strings (e.g., "") (let's call them
string blanks), and cells that contain nothing (let's call them empty
blanks). From the above two formulas, if the word "blank" means the
same thing in each, the sum of the results of the two formulas has to be
50. But if the range includes string blanks, the total won't be 50.
That's because the first formula counts both string blanks and empty
blanks, while the second counts both cells with data and cells with
string blanks, so that the total of the values returned by the two
formulas will exceed 50 by the number of cells containing string blanks.

Alan Beban

 

[Harlan Grove]

This highlights the problem of not being precise in distinguishing
between cells containing empty strings (e.g., "") (let's call them
string blanks), and cells that contain nothing (let's call them empty
blanks). From the above two formulas, if the word "blank" means the
same thing in each, the sum of the results of the two formulas has to be
50. But if the range includes string blanks, the total won't be 50.
That's because the first formula counts both string blanks and empty
blanks, while the second counts both cells with data and cells with
string blanks, so that the total of the values returned by the two
formulas will exceed 50 by the number of cells containing string blanks.

So use

COUNTIF(B1:B50,"=")

if you want to count blank cells (i.e., cells for which ISBLANK returns
TRUE) rather than cells evaluating to zero length strings (i.e., cells for
which LEN returns 0), and use

COUNTA(B1:B50)

if you want to count nonblank cells. If all cells would be blank or contain
text, and if cells evaluating to zero length strings should be considered
the same as blank, use

COUNTIF(B1:B50,"<>?*")

to count blank and zero length strings, and use

COUNTIF(B1:B50,"=?*")

to count strings with positive length.

 

[Alan Beban]

. . . If all cells would be blank or contain
text, and if cells evaluating to zero length strings should be considered
the same as blank, use

COUNTIF(B1:B50,"<>?*")

to count blank and zero length strings . . . .

Or simply =COUNTIF(B1:B50,"")

And both work if all cells would be blank or contain text or error values.

Alan Beban