4

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.

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

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

1

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,

  • This gives the same behavior. Countblank counts as intended while counta gives the number of cells in the range. – WickedMongoose Nov 16 '15 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.