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.

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.