Excel is one very powerful software. If you have any experience with it at your job or business, you may know just how capable it is. There are so many little tips and tricks that you can do with it. This guide will give you another very helpful tip. And I will also discuss what it is. It is the count if cell is not blank formula.
There are mainly three ways you can count cells that are not blank from a worksheet. The three functions are COUNTIF, COUNTA, and SUMPRODUCT. You will need to define the range and also the conditions for them to use.
The SUMPRODUCT function is a bit different. It does not count space characters or empty strings as blank cells. So, in some cases, it might give you the more correct result.
The Super Useful Count if Cell is Not Blank Formula
Sometimes you might have a datasheet in front of you with some cells left blank. Wait, it is not a mistake. It is intentional. Maybe there is no data to put there. Anything can be the case. But the point is it is blank and you have to work with it.
You need to count the cells. But the cells that need to be counted are cells that are not blank. Sounds tricky, does it not? Well, do not worry too much. It is simpler than it seems.
There are different methods to do it. And there is also a way to do the opposite. To count when the cell is blank. I wanted to make this an extensive and helpful guide. Let’s get started then.
Counting Cells That Are Not Blank
To better understand how each formula works and the use cases, I broke it down into sections. In this section, I am going to show you the ways you can count cells that are not blank. There are mainly three ways you can do it.
- COUNTIF function
- COUNTA function
- SUMPRODUCT function
The COUNTIF Function
COUNTIF functions count cells that meet the given criteria. This is, of course, within a range. It will ask for the range it needs to count and also the criteria it needs to check to make the count. Using this useful formula, you can also count non-blank cells. For that the formula will be:
=COUNTIF(range,”<>”)
Let’s see how you would use this formula in a real-life scenario. Say, you have a dataset of smartphones. Some have ongoing discounts on them and some do not. And they are all laid on a worksheet.
What if you wanted to count how many products have discounts? Well, if there are only a few models, you can just manually count them. But what if there are hundreds of phone models? Well, that is when the COUNTIF formula will come in handy.
If your data ranges from the say cell B2 all the way to B50, then the formula will be like this:
=COUNTIF(B2 : B50), “<>”
In this example, you are giving the formula the range to count, which is from cell B2 to cell B50. The “<>” is the indicator for blank cells and “ ” is not for non-blank cells. And the criteria is to not count blank cells. And it will give you the number of phones that have discounts.
The COUNTA Function
COUNTA is a pretty simple function. It will count the cells in a given range that are not empty. It is as simple as that. It is what you would call an argument function and just requires the range to count from. Here is what the basic COUNTA function formula looks like:
=COUNTA(range)
Let’s use this in our previous example. So, the cells that had the data we wanted were between the range of cell B2 and cell B50. So, what if you wanted to do the same with the COUNTA function? Well, it is pretty easy, you will just have this formula:
=COUNTA(B2 : B50)
The Problem with COUNTIF and COUNTA Functions
As useful as COUNTIF and COUNTA functions are, there is one caveat you need to keep in mind though. These two functions will also count spaces and any formula that returns an empty string.
Now, why is that a problem? Well, space and blank cells are not the same things. You will get different results with COUNTIF and COUNTA even though these cells just appear blank. In this case, you need to use the SUMPRODUCT function.
The SUMPRODUCT Function
The SUMPRODUCT function will give you the sum of the products. And it will do this within the supplied range as well. To count blank cells with this function, here is the formula:
=SUMPRODUCT(–(B2:B50<>””))
If you remember, in the example we said that cells B2 to B50 have data in them. And some phones are not discounted. So, there is no data on those cells. But what if there was a space character on one of the cells? COUNTIF and COUNTA will then give you a wrong answer.
This is where the SUMPRODUCT functions come in handy. The first part of the formula checks whether the cells in the given range are empty strings or not. The formula will return Boolean values with ‘True’ and ‘False’ where the criteria are met or not met.
The double negatives convert it into 0 and 1s and then give the real final answers of the cells that are not blank. However, this formula does not count as a blank cell, you need to use the TRIM functionality:
=SUMPRODUCT(–(TRIM(C2:C13)<>””))
Wrapping Up
And that is all you need to know about all the counts if cell is not blank formula in Excel. This little formula will come in handy in many different scenarios when you are presented with a datasheet with blank cells.
If you are required to see how many non-blank cells there are, you no longer have to check it manually now. Just use the function I showed you in this guide and it should become much easier for you.
