Why COUNTA counts cells that look empty in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(C2:C10<>""))Formulas that return "" aren't empty, so COUNTA counts them. Count cells that show something instead: =SUMPRODUCT(--(C2:C10<>"")).
- Result on the example
- 5 in
F1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
How it works
Every cell in column C holds a formula. When sales are under 1,000 the formula returns "", empty text, which looks blank but is still a value, so =COUNTA(C2:C10) returns 9. Comparing each cell with "" counts only the ones that show text: 5 bonuses.
=SUMPRODUCT(--(C2:C10<>""))Result5
C2:C109 rows × 1 col: "Bonus", "", "Bonus", "", …A range: 9 cells in one column.
C2:C10<>""{9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The <> operator tests whether they differ.
-(C2:C10<>""{9 rows × 1 col} -1, 0, -1, 0, …Makes the value negative.
--(C2:C10<>""{9 rows × 1 col} 1, 0, 1, 0, …Makes the value negative.
SUMPRODUCT(--(C2:C10<>""))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(C2:C10<>""{9 rows × 1 col} 1, 0, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(C2:C10<>"")). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIF(C2:C10,"?*") | 5 | Counts text with at least one character, so empty text is skipped. Works for text results only. |
| =ROWS(C2:C10)-COUNTBLANK(C2:C10) | 5 | COUNTBLANK treats empty text as blank, so all rows minus the blanks gives the same count. |
| =COUNTA(C2:C10) | 9Different result | The formula that seems wrong. Returns 9: it counts every formula, including those returning "". |
Common mistakes
- A cell with a space, or an apostrophe on its own, also looks empty and is counted. Search for them with
=SUMPRODUCT(--(TRIM(C2:C10)="")). - Copying formulas and pasting as values keeps the empty text. The cells stay "not blank" until you clear them.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why COUNTA counts cells that look empty
F1, count the reps whose Bonus cell in C2:C10 actually shows something.Questions
Why does COUNTBLANK count cells that ISBLANK says are not blank?
COUNTBLANK counts empty text as blank; ISBLANK returns FALSE for it, because the cell holds a formula.
Should the formula return 0 or "" for "nothing"?
Return "" when the cell is for display; return 0 or FALSE when other formulas will count or add it.
Why doesn't Excel treat "" as blank?
The cell contains a formula, so it isn't empty, even though the result is empty text.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.