Happy Friday and time for some more #formulafun on a Friday!. I recently was asked to find a way of knowing how many errors ( if any) were in an Excel worksheet. I came across this quick and easy formula which will return a count if all errors in a range of cells that we specify in the formula. This is great if you have a large number of formulas with multiple calculation types, which could throw up a number of different errors in their calculations.
With this peachy little formula, we can, in a flash, count the errors, and then decide if we want to ignore them or deal with them!
I have in my example a sheet that has a good few calculations on it. I then wanted to look at all of my cells in the work area (in this instance it is B6:E14) and see if any of my cells were returning an error from their calculations, and of course, count them.
To calculate the number of errors in the worksheet we can use the ISERROR along with the SUMPRODUCT formula. Let’s take a look at Formula Syntax for both of these formulas.
The SUMPRODUCT Formula Syntax
The SUMPRODUCT Formula multiplies corresponding components in the given arrays and returns the sum of those products and although SUMPRODUCT treats its arguments as arrays, it doesn’t need to be array-entered.
=SUMPRODUCT(array1, [array2], [array3], …)
where
array1 Required. The first array argument whose components you want to multiply and then add.
array2, array3,… Optional. Array arguments 2 to 255 whose components you want to multiply and then add.
The ISERROR Formula Syntax
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
=ISERROR(value)
where
The value that you want to test. If value is an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL), the ISERROR function will return TRUE. Otherwise, it will return FALSE.
Ok so let’s go ahead put both of these together and count how many errors we have in the example Excel worksheet.
The ISERROR part of the formula evaluates each cell in the range of cells and returns a TRUE for an ERROR and FALSE if no ERROR exists. We can see there are 2 TRUE returns from Excel.
Adding the — operator (or a double unary) coerces the TRUE/FALSE values to zeros and 1’s. The resulting array looks like this:
We can now use the SUMPRODUCT to SUM the items in the array and return the total which is 2. How clever is that huh?.
Over To You!
Have you used the — operator in your formulas or the SUMPRODUCT. Share in the comments below with other Excellers:-)
If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
Do You Need Help With An Excel Problem?.
I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST.
