# 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:
`=COUNTA(C5:C11)`
• 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:
`=COUNTIFS(C5:C11,"Arizona",D5:D11,"<>")`
• 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:
`=SUMPRODUCT(--(LEN(B5:D11)>0))`
• 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:
`=COLUMNS(B5:D11)*ROWS(B5:D11)-COUNTBLANK(B5:D11)`
• 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}

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF