Iserror for an array

  • Thread starter Thread starter diepvic
  • Start date Start date
D

diepvic

Hi,
can I use Iserror for an array?
like ISERROR(A2:A300)
I've tried this with a non-error array. The result is TRUE, which means
there's an error in the array. I'm quite confused.

Pls help.
 
What are you trying to do? Are you wanting to know if there are any errors
in the range?

=SUMPRODUCT(--(ISERROR(A2:A300)))
 
Oops..correction..

=0<>SUMPRODUCT(--(ISERROR(A2:A300)))

If this post helps click Yes
 
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
 
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.
 
Back
Top