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.

count errors example

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")

 

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.