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

dgd1212Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Assuming column A does not contain any blank cells then use:

=COUNTIF($K:$K, "0")+COUNTBLANK($K:$K)-COUNTBLANK($A:$A)

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
Ardhendu SarangiSr. Project ManagerCommented:
if you can post a sample spreadsheet, maybe there can be an alternative to what you are looking for...
0
 
zorvek (Kevin Jones)ConsultantCommented:
>Does the "1" suffice to count a non-blank cell?

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

Kevin
0
 
dgd1212Author Commented:
Thank You!!!! The formula worked perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.