A4 Accounting

Counting Errors in a Range in Excel

If you need to ensure that a range has no errors you can count the errors and compare the result to zero to ensure the range is error free.

The formula in cell C1 which counts errors in a range is

=SUMPRODUCT(ISERROR(A1:A5)*1)

If you want to display TRUE for no errors and FALSE for error(s) you can use

=SUMPRODUCT(ISERROR(A1:A5)*1)=0

You can also use an IF function to display the text Error if any errors are found.

=IF(SUMPRODUCT(ISERROR(A1:A5)*1)=0,"OK","Error")