How to count cells with errors in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--ISERROR(D2:D9))Test every cell with ISERROR and add up the TRUEs: =SUMPRODUCT(--ISERROR(D2:D9)).
- Result on the example
- 3 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(--ISERROR(D2:D9))Result3
D2:D98 rows × 1 col: $40.00, $249.00, #DIV/0!, #VALUE!, …A range: 8 cells in one column.
ISERROR(D2:D9){8 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …ISERROR: TRUE for any error.- value
D2:D98 rows × 1 col: $40.00, $249.00, #DIV/0!, #VALUE!, …
- value
-ISERROR(D2:D9){8 rows × 1 col} 0, 0, -1, -1, …Makes the value negative.
--ISERROR(D2:D9){8 rows × 1 col} 0, 0, 1, 1, …Makes the value negative.
SUMPRODUCT(--ISERROR(D2:D9))3SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--ISERROR(D2:D9){8 rows × 1 col} 0, 0, 1, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--ISERROR(D2:D9)). We ran it in Google Sheets mode on the same data and it returns 3.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(--ISERROR(D2:D9)) | 3 | In Excel 2021, Microsoft 365 and Google Sheets, SUM handles the array on its own. |
| =COUNTA(D2:D9)-COUNT(D2:D9) | 3 | 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.
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.
Count cells with errors
F2, count the cells in D2:D9 that show an error.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.
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.