Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting errors
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting errors
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting errors
On May 14, 3:27*am, kittronald 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))} |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting errors
Charabeuh,
Thanks, that did the trick ! I keep forgetting to use SUMPRODUCT to expand the contents of a multi-valued named range. - Ronald K. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting errors
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting errors
On May 17, 1:01*pm, kittronald 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of errors in a worksheet | Excel Worksheet Functions | |||
Counting with a condition disregarding errors | Excel Worksheet Functions | |||
Counting the number of "#N/A" errors | Excel Worksheet Functions | |||
Counting Errors | Excel Worksheet Functions | |||
Errors in COUNT, COUNTA, COUNTIF when counting merged cells | Excel Worksheet Functions |