The previous blog:

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**

**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.