How to Count Filled Cells in Excel (5 Quick Ways)

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:

➽ 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:

➽ Now just press the Enter button and you’ll get the result at once.

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.

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:

➽ 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:

➽ 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

<< Go Back to Count Cells | Formula List | Learn Excel