asked on

COUNTIF with blank and notblank

I have a spreadsheet with a column that has text. Some cells are blank.

I have these two formula's to count:
=COUNTIF($K:$K, "0")
=COUNTIF($K:$K, "1")

A blank cell should be counted as zero, but is not for some reason. Actually, I can not have all the blank cells counted as I am looking at the whole column. Need to stop looking for blanks when column I has its first blank cell.

To the main question...why aren't blank cells being counted?
and...
Does the "1" suffice to count a non-blank cell?

Thanks for any assistance

zorvek (Kevin Jones)

The problem is that there is no way to distinguish between a blank cell in your data rows versus a blank row below your data rows.

The only way to solve the problem is to reference only the range of cells representing your data rows. Or put zeros into the blank cells you do want to count.

Is there another column that does not contain any blank cells in the table? If so we can use that to do the calculation.

Kevin

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ardhendu Sarangi

if you can post a sample spreadsheet, maybe there can be an alternative to what you are looking for...

zorvek (Kevin Jones)

>Does the "1" suffice to count a non-blank cell?

If you mean will blank cells not be counted, then yes.

Kevin

dgd1212

ASKER

Thank You!!!! The formula worked perfect.