# How to count cells with errors in Excel

Test every cell with ISERROR and add up the TRUEs: `=SUMPRODUCT(--ISERROR(D2:D9))`.

**Excel:** `=SUMPRODUCT(--ISERROR(D2:D9))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Product | Revenue | Units | Unit price |  | Errors |
| Lamp | 1240 | 31 | =B2/C2 |  |  |
| Chair | 4980 | 20 | =B3/C3 |  |  |
| Desk | 0 | 0 | =B4/C4 |  |  |
| Stand | 890 | n/a | =B5/C5 |  |  |
| Shelf | 2150 | 43 | =B6/C6 |  |  |
| Mat | 0 | 0 | =B7/C7 |  |  |
| Hook | 312 | 52 | =B8/C8 |  |  |
| Cable | 455 | 91 | =B9/C9 |  |  |

Formula in F2: `=SUMPRODUCT(--ISERROR(D2:D9))`

Result: 3

## How it works

ISERROR returns TRUE for any error: #DIV/0!, #VALUE!, #N/A, #REF! and the rest. Applied to the whole column it returns one TRUE or FALSE per row, the double minus turns them into 1 and 0, and SUMPRODUCT adds them up.

Here two products have zero units (#DIV/0!) and one has "n/a" as its units (#VALUE!), so the count is 3.

## Other ways

- `=SUM(--ISERROR(D2:D9))`: In Excel 2021, Microsoft 365 and Google Sheets, SUM handles the array on its own.
- `=COUNTA(D2:D9)-COUNT(D2:D9)`: Non-empty cells minus numbers. Works when the column holds only numbers and errors, as a calculated column does.

## Common mistakes

- COUNTIF can count one error type, like `=COUNTIF(D2:D9,"#DIV/0!")` in Excel, but not all errors at once. ISERROR catches every kind.
- ISERR is not the same as ISERROR: ISERR ignores #N/A. Use ISNA to count only #N/A results from lookups.
- Hiding errors with IFERROR in column D makes them uncountable. Count first, or return a marker such as "check" that COUNTIF can count.

## Questions

**How do I count only #N/A errors?**

Use ISNA: `=SUMPRODUCT(--ISNA(D2:D9))`.

**How do I count cells that are not errors?**

Use NOT: `=SUMPRODUCT(--NOT(ISERROR(D2:D9)))`. In Excel you can also use `=AGGREGATE(3,6,D2:D9)`, which ignores errors.

**How do I list the rows with errors?**

`=FILTER(A2:A9,ISERROR(D2:D9))` in Excel 365 or Google Sheets.

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/count-cells-with-errors