SumProduct When Range Has Errors

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using this formula.

=SUMPRODUCT(--($C$3:$C$180=I3);--($F$3:$F$180>0))/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.
 
Does this do it

=SUM(IF(ISNUMBER($F$3:$F$18),($C$3:$C$18=I3)*($F$3:$F$18>0)*(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)
 
Back
Top