Solved

COUNTIF with blank and notblank

Posted on 2011-02-27
5
386 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:dgd1212
  • 3
5 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
Comment Utility
Assuming column A does not contain any blank cells then use:

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

Kevin
0
 
LVL 20

Expert Comment

by:pari123
Comment Utility
if you can post a sample spreadsheet, maybe there can be an alternative to what you are looking for...
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
>Does the "1" suffice to count a non-blank cell?

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

Kevin
0
 

Author Closing Comment

by:dgd1212
Comment Utility
Thank You!!!! The formula worked perfect.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now