Bizarre COUNTIF/COUNTA behavior in excel for counting text cells that are not ""

Question

I have a column of cells filled with formula that give "" on error, and a text string if no error. In a second column I'm giving a formula with:

=COUNTIF(range,"<>")

as well as:

=COUNTA(range)

I would expect either of these to count only cells that do not contain "" but it counts all of the cells. Strange, maybe it doesn't count cells with formula as "" even if that is what the formula yields, so I test with:

=COUNTIF(range,"")

as well as:

=COUNTBLANK(range)

Both of which yields the appropriate number of cells that contain "".

A solution is to simply subtract the total count from the count of "" but that is not elegant, just wondering if there is something I'm doing wrong or if others do not see this behavior.

Answer 1

=SUMPRODUCT(--(range<>""))

Will give the you the number of non-blank cells in the range, ignoring both empty strings ("") and empty cells.

Answer 2

To count values use the COUNTA function.

So =COUNTA(Range)

This will give you the number of values in your range.

The opposite can be achieved using =COUNTBLANK(Range)

This will provide a count of blank cells in your range.

Cheers,