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)
![Image1.jpg]()
Picture 1 (Problem is to count all the errors
in erroneous column)
Solution
![Image2.jpg]()
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.