Counting errors

[kittronald]

In Microsoft Excel 2007, how can you count the number of Excel errors
(i.e., #VALUE!, #N/A, etc.) in a named range ?

Using =COUNTIF(named_range,"#*") doesn't work with Excel error values.



- Ronald K.

 

[Charabeuh]

Hello,

Try this formula:
=SUMPRODUCT( --ISERROR(named_range) )

 

[Ram]

In Microsoft Excel 2007, how can you count the number of Excel errors
(i.e., #VALUE!, #N/A, etc.) in a named range ?

Using =COUNTIF(named_range,"#*") doesn't work with Excel error values.

- Ronald K.

Hi You can use following array formula also

={SUM(ISERROR(named_range)*1)}
or
={SUM(IF(ISERROR(named_range),1,0))}

 

[kittronald]

Charabeuh,

Thanks, that did the trick !

I keep forgetting to use SUMPRODUCT to expand the contents of a
multi-valued named range.



- Ronald K.

 

[kittronald]

Ram,

Thanks for the reply.

I try to stay away from array entered formulas since they
collectively slow my computer down.

I appreciate the solution though.


- Ronald K.

 

[joeu2004]

I try to stay away from array entered formulas since they
collectively slow my computer down.

I have several reasons for avoiding array formulas, but performance is
not one of them.

Comparing SUMPRODUCT(--ISERROR(range)) to the array formula SUM(--
ISERROR(range)), I find that the SUM formula is about 55 times faster
than SUMPRODUCT for a range of 100 cells and about 390 times faster
for a range of 10000 cells on my system [*].

Nevertheless, we are talking about very small times per formula (less
than 2 msec on my computer).

My primary reason for avoiding single-cell array formulas is that they
are error-prone. Often, they will appear to work (return a value
instead of an error) if we press Enter instead of ctrl+shift+Enter,
resulting in a non-array formula.