Sometimes we need to count cells that are not empty.
While you may be able to do it manually with a small dataset, it could be quite difficult to do it manually with a large dataset.
And given the amazing functions in Excel, there is no reason not use a formula to do this.
It not only saves your time and also gives more accurate results for you.
In this tutorial, I’ll show how to quickly and easily count non-blank cells in Excel.
We’ll go over a few different methods for counting non-blank cells.
COUNTA Function to Count Cells that are Not Blank in Excel
Excel COUNTA function helps to count cells that are not blank.
The below data set shows the sales figures for ten different ice cream flavors. Column A displays the ice cream flavors, and Column B displays the sales of each ice cream flavor.
Now, I need to figure out how many different ice cream flavors contributed to total sales.
Therefore, I need to identify the ice cream flavors whose sales figures do not contain any blank cells.
Below COUNTA formula will help to count the number of flavors that contributed to sales.
=COUNTA(B2:B11)
The syntax of the COUNTA function is COUNTA(value1, [value2], …).
The value1 reflects the COUNTA function’s first argument. You must choose which values to count here. If you want, you can add more values to count as additional arguments, up to a maximum of 255.
In this scenario, we need to count the values in cells B2 through B11. As a result, we choose that range as the function’s first argument.
Also read: How to Count Between Two Numbers in Excel?
COUNT function to Count Cells that are Not Blank in Excel
The COUNT function can be used to count the number of non-blank cells in a range of just numbers.
The below data set shows the sales figures for ten different ice cream flavors.
Column A displays the ice cream flavors, and Column B displays the sales of each ice cream flavor.
Now I need to figure out how many different ice cream flavors contributed to total sales.
Therefore, I need to identify the ice cream flavors whose sales figures do not contain any blank cells.
The COUNT function can be used because the sales column only contains numbers, and we need to calculate any non-blank cells in that column.
The number of flavors that contributed to sales will be counted using the COUNT formula below.
=COUNT(B2:B11)
The syntax of the COUNT function is COUNT(value1, [value2], …).
In this case, we need to count values in B2 to B11 cells. Hence, we select that range as the first argument of the function.
This COUNT function cannot be used if you need to count non-blank cells in a range that contains text, logical values, and error values as well.
Also read: How to Count Filtered Rows in Excel?
SUBTOTAL function to Count Cells that are Not Blank in Excel
Excel SUBTOTAL function is another function that can be used to count cells that are not blank in Excel.
The SUBTOTAL function is really useful when you want to count non-blank cells after filtering data.
The below data set shows the sales figures for ten different ice cream flavors.
Column A displays the ice cream flavors, and Column B displays the sales of each ice cream flavor.
Now, I need to identify the ice cream flavors whose sales figures do not contain any blank cells.
Below SUBTOTAL function will help to count the number of flavors that contributed to sales.
=SUBTOTAL(3,B2:B11)
The syntax of the SUBTOTAL function is SUBTOTAL(function_num,ref1,[ref2],…).
The function_num is the function number that we have to use. In this case, we want to use the COUNTA function. So, the relevant function_num is 3.
Next, from ref1 onwards, we can specify the cell references for which we want the subtotal. Therefore, we are selecting cells B2 to B11 as the cell references.
Excluding Hidden Cell Counting
Let’s assume that the Mint flavor is a new flavor, and you hide it as you want to see how many flavors contributed to sales, excluding the new flavor.
If you use function_num 3, you will get the same count as the above example.
If you want to exclude the manually hidden cells for your count, you have to use the function_num 103 for the SUBTOTAL function.
COUNTIF function to Count Cells that are Not Blank in Excel
Excel’s COUNTIF function can be used to count cells that are not blank in Excel.
The COUNTIF function helps to count cells as per a given criterion.
The below data set shows the sales figures for ten different ice cream flavors. Column A displays the ice cream flavors, and Column B displays the sales of each ice cream flavor.
Now, I need to identify the ice cream flavors whose sales figures do not contain any blank cells.
Below COUNTIF formula will help to count the number of flavors that contributed to sales.
=COUNTIF(B2:B11,"<>"&"")
The syntax of the COUNTIF function is COUNTIF(range, criteria).
The range refers to the number of values that you need to count. In this scenario, the range is cells B2 to B11.
The criteria refer to what you need to count. Since we need to count cells that are not blank, we enter the not equals sign “<>” and blank sign “”. We must combine them using an ampersand (&) sign.
The logical operator, the not equals sign <> must enter within quotes.
SUMPRODUCT function to Count Cells that are Not Blank in Excel
We can count the non-blank cells in Excel with the SUMPRODUCT function.
The below data set shows the sales figures for ten different ice cream flavors. Column A displays the ice cream flavors, and Column B displays the sales of each ice cream flavor.
Now, I need to identify the ice cream flavors whose sales figures do not contain any blank cells.
Below SUMPRODUCT formula will help to count the number of flavors that contributed to sales.
=SUMPRODUCT(--(B2:B11<>""))
Let’s have a look at how this formula works.
The inner bracket of the formula, i.e. (B2:B11<>””) returns an array of true and false values for the selected range.
The true and false value is based on the given criteria, i.e., <>”” (not equal to blank).
Then the double negative sign (–) will convert these true values to 1s and false values to 0s.
SUMPRODUCT function + LEN function to Count Cells that are Not Blank in Excel
You can count non-blank cells in Excel by combining the SUMPRODUCT function with the LEN function.
The below data set shows the sales figures for ten different ice cream flavors.
Column A displays the ice cream flavors, and Column B displays the sales of each ice cream flavor.
Now, I need to identify the ice cream flavors whose sales figures do not contain any blank cells.
Below formula will help to count the number of flavors that contributed to sales.
=SUMPRODUCT(--(LEN(B2:B11)>0))
Let’s look at how this formula works. The LEN(B2:B11) will generate the number of characters in each cell in the given cell range.
So, in this scenario, it will give the number of characters for each cell in B2 to B11.
Then, the formula will convert the number of characters to true or false values based on the given criteria.
As we have entered “>0” as the condition to count non-blank cells, all cells with more than 0 characters will get “True” and the other cells will get “False”.
Finally, double negative values will convert true values to 1s and false values to 0s.
ROWS function + COLUMNS function to Count Cells that are Not Blank in Excel
You can count non-blank cells in Excel by combining ROWS and COLUMNS functions.
The below data set shows the sales figures for ten different ice cream flavors.
Column A displays the ice cream flavors, and Column B displays the sales of each ice cream flavor.
Now, I need to identify the ice cream flavors whose sales figures do not contain any blank cells.
Below formula will help to count the number of flavors that contributed to sales.
=ROWS(B2:B11)*COLUMNS(B2:B11)-COUNTBLANK(B2:B11)
In this formula, first, we count the total number of cells using the ROWS and COLUMNS functions. The syntax of the ROWS function is ROWS(array).
The syntax of the COLUMNS function is COLUMNS(array).
When we multiply the number of rows and the number of columns, we get the total number of cells in the range.
Then we subtract the total number of blank cells in the total cells. The COUNTBLANK function is used to count the total number of blank cells.
The syntax of the COUNTBLANK function is COUNTBLANK(range).
In this scenario, the total number of rows is 10, and the total number of columns of 1.
So, there are 10 cells (10 rows ✖ 1 column). Then there are 3 blank cells that will be deducted from the total number of cells. The final answer is the count of the non-blank cells in the range.
In this article, I have recovered different ways to count non-blank cells in Excel. You can do this using multiple different functions and can decide which one to use based on your dataset.
Other Excel articles you may also like:
- Count Cells Less than a Value in Excel (COUNTIF Less)
- How to Count Cells with Text in Excel?
- How to COUNTIF Partial Match in Excel?
- COUNTIF Greater Than Zero in Excel
- Count the Number of Yes in Excel (Using COUNTIF)
- How to Count Unique Values in Excel
- Fill Blank Cells with 0 in Excel
- Fill Blank Cells with Dash (-) in Excel
