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

Method 1 – Using the COUNTA Function to Count Filled Cells in Excel

We’ll use a sample dataset of sales in different states. Some cells are empty. We’ll count the filled cells of Column C.

Steps:

• Select cell D13.
• Insert the formula given below:

• Press the Enter button.

Method 2 – Excel COUNTIFS Function to Count Filled Cells Based on Criteria

We’ll count the cells in the state column that have the fixed value Arizona.

Steps: 

• Use this formula in cell G5:

• Press the Enter button.

Method 3 – Applying the Find and Replace Tool to Count Filled Cells in Excel

Steps:

• Select the range of cells B5:D11.
• Press Ctrl + F. A dialog box for the Find and Replace tool will appear.
• Put * in the Find What box.
• Select Formulas from the Look in drop-down bar.
• Press Find All.

• The extension bar shows the total number of filled cells found.

• Select the locations of all cells from the dialog box and Excel will highlight the filled cells in the dataset.

Method 4 – Combining Excel SUMPRODUCT and LEN Functions to Count Filled Cells

Steps:

• Use the following formula in cell D13:

• Hit the Enter button.

Breakdown of the Formula:

➥ LEN(B5:D11)>0

Checks 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))

The SUMPRODUCT function will show the number of filled cells found.

Method 5 – Entering a Special Formula to Count All Filled Cells in Excel

Steps:

• Use this formula in cell G5:

• 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)

Counts the number of rows in the range (B5:D11) and returns:

{7}

➥ COLUMNS(B5:D11)

Counts the number of columns in the range (B5:D11) and returns:

{3}

➥ COLUMNS(B5:D11)*ROWS(B5:D11)-COUNTBLANK(B5:D11)

The function subtracts the number of empty cells from the multiplication product of rows and columns numbers:

{16}

Download the Practice Workbook

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