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.

Download Practice Book

Download the Excel workbook that we’ve used to prepare this article.

5 Quick Methods to Count Filled Cells in Excel

Method 1: Use 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

Method 2: Insert COUNTIFS Function in Excel to Count Filled Cells 

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.

Read more: Excel Count Number of Cells in Range

Method 3: Apply ‘Find And Replace’ Tool of Excel to Count Filled Cells

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 is showing the total number of filled cells found.

Step 2:

➽ Then select 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

Method 4: Combine 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 Enter button

👇 Breakdown of the Formula:

➥ LEN(B5:D11)>0

It will check the cells whether it has 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}

Read more: Excel Count Cells with Numbers

Method 5: Enter a Special Excel 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 cells number from the multiplication product of rows and columns number. Then the result will return as-

{16}

Read more: Count Empty Cells in Excel

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

Related Articles

• Excel Formula to Count Cells with Text
• Count If a Cell Contains Any Text in Excel (4 Methods)
• How to Count Rows with Value in Excel (8 Ways)
• Count Rows with VBA in Excel (5 Approaches)
• How to Count Blank Cells in Excel (5 Ways)
• How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)