Hi,

First, if you want to know if there is an error in a range you could use

=ISERROR(SUM(A2:A300))

which would return TRUE if there was any error in the range.

Second, if you would be happy just to know if there is an error you could use

=SUM(A2:A300)

Which will return the error if there is one otherwise it will return an error.

Third, if you want to count the number of only one kind of error such as
#DIV/0! then you could use

=COUNTIF(A2:A300,"#DIV/0!")

Finally, the formulas
=SUMPRODUCT(--ISERROR(A2:A300)) or a similar function
=SUMPRODUCT(--ISERR(A2:A300))
works like this: the ISERROR function returns a string of TRUE's and FALSE's
which indicate for each cell in the range whether it is an error. The first
minus (right one) causes Excel to convert TRUE to -1 and FALSE to 0, the
second minus converts the -1 to 1. Then the SUMPRODUCT sums all the 1's.
You could enter the array formula
=SUM(--ISERR(A2:A300)) but this requires array entry (pressing
Shift+Ctrl+Enter) so to avoid that it is common to use SUMPRODUCT.
SUMPRODUCT also calculates somewhat faster.



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"diepvic" wrote:

Thanks Jacob & T.Valko,

It works so perfectly
Anw, could you pls explain for me how it works? What does "--" mean here?

Thanks a lot