MS Excel: How to use the ISERROR Function to Test for Errors in a Row
This Excel tutorial explains how to use the Excel ISERROR function to test for errors in a row with screenshots and instructions.
Description
The Microsoft Excel ISERROR function can be used to check for error values such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL. As such, you can use the ISERROR function to test for errors in a row of a worksheet. Let's explore how to do this.
If you want to follow along with this tutorial, download the example spreadsheet.
Example
Using the ISERROR function to count the number of errors in a row is not as straight-forward as you may think. Because the ISERROR function will only accept 1 value as a parameter, you will need to use an array formula to test an entire row of values.
Let's show you how to count the number of errors in row 3 of a spreadsheet. Based on the spreadsheet below:
In cell A7, we've created the following array formula that uses the ISERROR function with the SUM function to count the number of errors in row 3:
=SUM(ISERROR(3:3)*1)
When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:
{=SUM(ISERROR(3:3)*1)}
This formula will check each value in row 3 for an error using the ISERROR function and return each error as a value of 1. The SUM function will then count those errors and return the total number of errors found in row 3.
In this example, there are 2 errors in row 3 - both cell B3 and cell E3 contain the #DIV/0! error.
More Examples
Here are more examples that show how to use the ISERROR function in Excel:
Advertisements