Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct When Range Has Errors
I am using this formula.
=SUMPRODUCT(--($C$3:$C$180=I3);--($F$3:$F$1800))/COUNTIF($C$3:$C$180;I3) Sometimes the ranges have value like: #N/A #VALUE! #NUM! Is there a way to have make the formula "ignore" the cells w/ errors ? Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct When Range Has Errors
Does this do it
=SUM(IF(ISNUMBER($F$3:$F$18),($C$3:$C$18=I3)*($F$3 :$F$180)*(ROW($C$3:$C$18)^0)))/COUNTIF($C$3:$C$18,I3) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "carl" wrote in message ... I am using this formula. =SUMPRODUCT(--($C$3:$C$180=I3);--($F$3:$F$1800))/COUNTIF($C$3:$C$180;I3) Sometimes the ranges have value like: #N/A #VALUE! #NUM! Is there a way to have make the formula "ignore" the cells w/ errors ? Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore errors in range | Excel Worksheet Functions | |||
New Errors in Sumproduct Usage | Excel Worksheet Functions | |||
Help on sumproduct returning errors | Excel Worksheet Functions | |||
Ignoring text and errors with Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions |