While using Excel we need to count filled cells for different calculations. In this article, I’m going to show how to count filled cells in Excel with 5 quick methods. Just have a look at the following methods properly and you’ll find them useful to apply.
1. Using COUNTA Function to Count Filled Cells in Excel
Let’s get introduced to our dataset first. Here I have used 3 columns and 7 rows to show some salespersons’ sales in different states. You’ll notice that some cells are empty. Now we’ll count the filled cells of Column C using the COUNTA function. COUNTA function is used to count the non-blank cells.
Steps:
âž½ Activate Cell D13
âž½ Type the formula given below:
=COUNTA(C5:C11)
âž½ Then press the Enter button.
And we’ve got the number of filled cells in Column C is 4
2. Excel COUNTIFS Function to Count Filled Cells Based on Criteria
Now let’s count the filled cells using the COUNTIFS function. This function is used to count the number of cells that meet multiple criteria in the same or different ranges. Here I’ll count the cells of Arizona state with sales values.
Steps:Â
âž½ Type the formula in Cell G5 which is given below:
=COUNTIFS(C5:C11,"Arizona",D5:D11,"<>")
➽ Now just press the Enter button and you’ll get the result at once.
Read More: How to Count Non Blank Cells with Condition in Excel
3. Applying ‘Find And Replace’ Tool to Count Filled Cells in Excel
In this method, we’ll use the Find and Replace tool to count the filled cells. Let’s see how to use it.
Step 1:
âž½ Select the range of cells: B5 to D11.
âž½ Press Ctrl+F. A dialog box of the Find and Replace tool will appear.
➽ Type ‘*’ in the Find What box.
âž½ Select Formulas from the Look in drop-down bar.
âž½ Finally, press Find All.
See the image below, the extension bar shows the total number of filled cells found.
Step 2:
âž½ Then select the locations of all cells from the dialog box and it will highlight the filled cells in the dataset.
Read More: Count Cells That Are Not Blank in Excel
4. Combining Excel SUMPRODUCT and LEN Functions to Count Filled Cells
Now we’ll use a combination of the SUMPRODUCT and the LEN functions to count the filled cells. The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays and the LEN function is used to return the length of a given text string. We’ll use the combination of both to find all filled cells in the whole data range.
Steps:
âž½ Type the formula in Cell D13 which is given below:
=SUMPRODUCT(--(LEN(B5:D11)>0))
âž½ Hit the Enter button
👇 Breakdown of the Formula:
➥ LEN(B5:D11)>0
It will check the cells whether have at least one character or not. And it will return as:
{TRUE,TRUE,TRUE;TRUE,FALSE,TRUE;TRUE,TRUE,FALSE;TRUE,FALSE,TRUE;TRUE,TRUE,FALSE;TRUE,FALSE,TRUE;TRUE,TRUE,TRUE}
➥ –(LEN(B5:D11)>0)
This formula will show the previous result in binary condition as shown below:
{1,1,1;1,0,1;1,1,0;1,0,1;1,1,0;1,0,1;1,1,1}
➥ SUMPRODUCT(–(LEN(B5:D11)>0))
Finally, the SUMPRODUCT function will show the number of filled cells found that will return as:
{16}
5. Entering a Special Formula to Count All Filled Cells in Excel
In this last method, I’ll use a special formula to count all filled cells. That is actually a combination of the COLUMNS, ROWS, and COUNTBLANK functions. The COLUMNS function is used to count the column numbers in a range. The ROWS function is used to count the row numbers in a range. And the COUNTBLANK function counts the empty cells.
Steps:
âž½ Type the formula in Cell G5 which is given below:
=COLUMNS(B5:D11)*ROWS(B5:D11)-COUNTBLANK(B5:D11)
âž½ Then just hit the Enter button to get the result.
👇 Breakdown of the Formula:
➥ COUNTBLANK(B5:D11)
This formula will count the empty cells in the range (B5:D11). It will return as:
{5}
➥ ROWS(B5:D11)
It will count the number of rows in the range (B5:D11) and will return as:
{7}
➥ COLUMNS(B5:D11)
It will count the number of columns in the range (B5:D11) and will return as:
{3}
➥ COLUMNS(B5:D11)*ROWS(B5:D11)-COUNTBLANK(B5:D11)
Finally, it will subtract the empty cell numbers from the multiplication product of rows and columns numbers. Then the result will return as:
{16}
Download Practice Book
Download the Excel workbook that we’ve used to prepare this article.
Conclusion
I hope all of the methods described above will be effective enough to count filled cells in Excel. Feel free to ask any questions in the comment section and please give me feedback