Excel Tips and Tricks 2

The previous blog:

Excel Tips and Tricks 1

Excel Tips n Tricks -Tip 2 (Count all the errors in a given range)

Description

In this tip You'll learn how to count all the errors in a given data range in a worksheet.

Problem

You are asked to count all the errors that lie in a Data Range. Here in picture 1 you can see we have some data in Range D3:F11. Errors you see here are produced intentionally just to demonstrate you how it works. Third column is named as "Erroneous Column" again intentionally as contains the cells having errors. So we've got a range of F3:F11 where we have to check and count for all the errors (could be thousand of records)

Picture 1 (Problem is to count all the errors in erroneous column)

Solution

Picture 2 in above shows you the solution but it includes two variants of count i.e. "Including #NA" and "Without #NA". Before I start explaining how the formula works behind the scene I would like to explain these two variants for those who not aware of.

In excel we have two functions - "ISERR()" & "ISERROR" to track if any error is there in cell.

Both looks same but behaves a little different. Both checks if cell has an error but "ISERR()" does not include "#NA" errors. Whereas "ISERROR()" checks for all the errors including "#NA" type errors.

Let's tear down the formula now…

Formula Explained

Solution to this problem in cell B3 & B4. B3 says: "=SUMPRODUCT (--ISERROR (F3:F11))".

Like I always prefer, starting from inside, the "ISERROR(F3:F11)" will return an array of TRUE and FALSE and the resultant array would be {TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE}.

One more thing you see before this block of code is a double hyphen (dash) "- -". By default SUM function cannot sum up the TRUE & FALSE in an array, to achieve this, it has to be coerced to its value i.e. 1 & 0 respectively. You can test this fact by yourself also.

Try to perform this sum operation:

"=SUM(TRUE, TRUE, FALSE)" => This will output the result as 2. (Here we're using TRUE/FALSE as separate numbers, they're not in an array, calculated as "SUM (1, 1, 0)")

Now try to perform the sum operation on an array of TRUE/ FALSE:

"=SUM({TRUE,TRUE,FALSE})"=> This will output the result as 0 (No sum is performed here);

Now to coerce the values inside an array we'll use double dash (--). Try performing the same operation as above adding double dash just before the array i.e.:

"=SUM (--{TRUE, TRUE, FALSE})"=> Voila! The output is there. You get 2 as result which is correct.
 
I think the idea behind double dash is clear to you. Let's move forward. So far we have

"=SUMPRODUCT(--{TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE})"

Evaluating the double dash, coercing the values inside array we get

"=SUMPRODUCT ({1, 1, 1, 1, 1, 1, 0, 0, 0})" which is going to result 6 as output

The cell B4 contains the same code but use “ISERR()” that does not check for “#NA” type of errors. We have one “#NA” error in given range, hence you see the count of errors without “#NA” is one less than the count of all errors in the given range.

Sample sheet is attached with this post, please see for any clarification.