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

Get FREE Advanced Excel Exercises with Solutions!

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.

Method 1: Use COUNTA Function to Count Filled Cells in Excel

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.

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

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.

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

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

Method 4: Combine SUMPRODUCT and LEN Functions to Count Filled Cells

👇 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.

Method 5: Enter a Special Excel Formula to Count All Filled Cells in Excel

👇 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


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo