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}
Download the Practice Workbook
<< Go Back to Count Cells | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
How do I get a count of multiple entered values in 1 cell. Assume that cell C10 has 20, 4 entered in it. I want to count that as 2 entries. Secondarily, how could I add the 2 values entered in 1 cell? In this case, that result would be 24.
Hello Dan K,
Counting Multiple Entries in One Cell: To count the number of values in a single cell (assuming they are separated by a comma and space like “20, 4”), use this formula:
=LEN(C10)-LEN(SUBSTITUTE(C10, “,”, “”))+1
Explanation:
LEN(C10): Counts the total number of characters in the cell.
SUBSTITUTE(C10, “,”, “”): Removes all commas from the text.
LEN(C10) – LEN(SUBSTITUTE(C10, “,”, “”)): Counts how many commas were removed.
Adding +1 gives the total number of entries.
Adding the Values in One Cell: To sum the numbers entered in a single cell, use this formula: “, “//s”))
=SUM(FILTERXML(“
” & SUBSTITUTE(C10, “, “, ““) & “Note: This formula only works in Excel 2013 and later versions with XML functions.
Alternatively, if you want a VBA solution, you can use a macro to split and sum the values.
Regards
ExcelDemy