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.

Hello,

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



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

On May 14, 3:27*am, kittronald <kittron...@yahoo.com> wrote:
> 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))}

Charabeuh,

Thanks, that did the trick !

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



- Ronald K.

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.

On May 17, 1:01*pm, kittronald <kittron...@yahoo.com> wrote:
> 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.


-----[*] XL2003 on WinXP. Single-core 2.127 GHz Pentium M processor.