Using COUNTIF with arrays

[Peter G A]

I want to check for errors in a sheet. To test the
concept I defined an array, Arr, which contained various
different values including some error values.

I defined another equal sized array, Arr_err, with the
array formula {=ISERROR(Arr)}, which gave me a set of
TRUE, FALSE values. The formula =COUNTIF(Arr_err, TRUE)
gives a count of the error values.

I do not want to have to use an intermediate array
because this gets impossible where the arrays are being
manipulated, so I tried combining the formulae as

{=COUNTIF(ISERROR(Arr), TRUE)}

but Excel does not allow this. MS Knowledge Base #214286
seems to imply that COUNTIF doesn't allow an array
argument.

I'm using Excel 2002.

Thanks for any help.

Peter G A

 

[Peter G A]

Thanks Alan - your solution gives a zero result, but it
prompted me to try:

{=SUM(IF(ISERROR(Arr), 1, 0))}

which works fine.

Peter G A

 

[Harlan Grove]

Thanks Alan - your solution gives a zero result, but it
prompted me to try:

{=SUM(IF(ISERROR(Arr), 1, 0))}

which works fine.

....

If entered correctly, =SUMPRODUCT(--ISERROR(Arr)) gives the same result.
Note that the two negative signs are *NOT* optional.
=SUMPRODUCT(ISERROR(Arr)) will *ALWAYS* evaluate to zero.

 

[Alan Beban]

You already have a couple of solutions, but for what it's worth, if the
functions in the downloadable file at http://home.pacbell.net/beban are
available to your workbook, the following, array entered, will also work:

=ArrayCountIf(ISERROR(Arr),TRUE)

Alan Beban