# Why COUNTA counts cells that look empty in Excel

Formulas that return "" aren't empty, so COUNTA counts them. Count cells that show something instead: `=SUMPRODUCT(--(C2:C10<>""))`.

**Excel:** `=SUMPRODUCT(--(C2:C10<>""))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Rep | Sales | Bonus |  | Bonuses |
| Ana | 1250 | =IF(B2>=1000,"Bonus","") |  |  |
| Ben | 800 | =IF(B3>=1000,"Bonus","") |  |  |
| Cara | 1430 | =IF(B4>=1000,"Bonus","") |  |  |
| Dev | 990 | =IF(B5>=1000,"Bonus","") |  |  |
| Eli | 2100 | =IF(B6>=1000,"Bonus","") |  |  |
| Fay | 640 | =IF(B7>=1000,"Bonus","") |  |  |
| Gus | 1005 | =IF(B8>=1000,"Bonus","") |  |  |
| Hana | 720 | =IF(B9>=1000,"Bonus","") |  |  |
| Ivo | 1890 | =IF(B10>=1000,"Bonus","") |  |  |

Formula in F1: `=SUMPRODUCT(--(C2:C10<>""))`

Result: 5

## 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.

## Other ways

- `=COUNTIF(C2:C10,"?*")`: Counts text with at least one character, so empty text is skipped. Works for text results only.
- `=ROWS(C2:C10)-COUNTBLANK(C2:C10)`: COUNTBLANK treats empty text as blank, so all rows minus the blanks gives the same count.
- `=COUNTA(C2:C10)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/counta-counts-blank-looking-cells