Count Cells That Contain Errors in Excel

by

To count how many cells in a range show an error, use =SUMPRODUCT(--ISERROR(E3:E12)). You can also count one error type, such as #N/A, or count only the cells that are not errors.

All errorsSUMPRODUCT + ISERROR
One error typeCOUNTIF
Works inAll versions

Count all errors

=SUMPRODUCT(--ISERROR(E3:E12))
SUMPRODUCT and ISERROR counting error cells
Step Result
ISERROR(E3:E12) {FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}
-- turns TRUE/FALSE into 1/0 {0;0;1;0;0;1;0;1;1;0}
SUMPRODUCT adds them 4

In Excel 365 and 2021, =SUM(--ISERROR(E3:E12)) works too.

SUM and ISERROR counting errors in Excel 365

Variations

To count Formula
Only #N/A =COUNTIF(E3:E12,"#N/A")
Only #DIV/0! =COUNTIF(E3:E12,"#DIV/0!")
Errors except #N/A =SUMPRODUCT(--ISERR(E3:E12))
Cells that are not errors =SUMPRODUCT(--NOT(ISERROR(E3:E12)))

ISERROR counts every error; ISERR skips #N/A; ISNA counts only #N/A.

Select them instead

To see where the errors are, select the range, press Ctrl + G → Special → Formulas, tick only Errors, and click OK.

Related