How to Count Cells That Are Not Empty in Excel

A column of 99 rows should hold 99 responses, and the count comes back as 84. Nothing is missing from the sheet: COUNTA counts anything a cell holds, including a formula that returns an empty string, so the two numbers disagree for a reason that is invisible on screen.

Count every filled cell with COUNTA

Click an empty cell and enter:

=COUNTA(B2:B100)

• B2:B100: the range to check.

Every cell holding text, a number, a date, a space, or an error is counted. If 84 of those 99 cells hold something, the result is 84.

Count only the numbers

COUNT ignores text and counts numeric entries, dates included:

=COUNT(B2:B100)

Run it beside COUNTA on a column that should be all numbers. A gap between the two results means some entries are text, usually numbers imported as text.

Count the empty cells

=COUNTBLANK(B2:B100)

This counts truly empty cells and, unlike COUNTA, also counts cells holding a formula that returns "". COUNTA plus COUNTBLANK will therefore exceed the range size when such formulas are present.

Treat formula blanks as empty

A cell containing =IF(A2>0,A2,"") looks empty but COUNTA counts it. To count only cells with visible content, use:

=SUMPRODUCT(--(B2:B100<>""))

• B2:B100<>"": tests each cell for real content, returning TRUE or FALSE.
• --: converts those results to 1 and 0 so they can be added.

=COUNTIF(B2:B100,"<>") behaves like COUNTA here and still counts the formula blanks, so SUMPRODUCT is the reliable version.

Read the count on the status bar

Select the range and look at the bottom right of the window. Count shows the number of non-empty cells in the selection with no formula at all, which settles a one-time question in a second.

Add the missing status bar figures. Right-click the status bar and tick Count, Numerical Count, and Sum to see all three at once.

Clean imported data first. A cell holding nothing but a space counts as content. Run =TRIM(B2) down a helper column and point the SUMPRODUCT formula at that column instead: TRIM turns a space-only cell into an empty string, which SUMPRODUCT then treats as blank.

Sources. COUNTA function, COUNT function, COUNTBLANK function.