Its really best to do your Error Handling at the source. Whichever formulas
are generating the #VALUE errors (or any error for that matter) should be
re-written to allow for this. Such as:

=IF(ISERROR(your forumla),"Error",your formula)

This will allow you to still be notified when an error occurs, but will also
allow Excel to continue to perform calculations on your results since they're
now simple text values rather than an usuable error.

But, if you really want bypass this advice, you might be able to get away
with something like:

=SUMPRODUCT(--(IF(ISERROR(Main!$C$2:$C$266),0,Main!$C$2:$C$266)= $C2),--(IF(ISERROR(Main!I$2:I$266),0,Main!I$2:I$266))/SUMPRODUCT(--(IF(ISERROR(Main!$C$2:$C$266),0,Main!$C$2:$C$266)= $C2),--(IF(ISERROR(Main!I$2:I$266),0,Main!I$2:I$266)<"") ))

This would be entered as an ARRAY formula, so press CTRL-SHIFT-ENTER instead
of just ENTER.

HTH
Elkar


"Andrew" wrote:

Here is the current formula for a cell:

=SUMPRODUCT(--(Main!$C$2:$C$266=$C2),Main!I$2:I$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C2),--(Main!I$2:I$266<""))

The problem is that when it finds DIV/0 or #VALUE errors anywhere in the
column it is searching, it then returns a #VALUE error. I would like to make
it more robust and able to still give a numeric return despite errors in some
of the cells it is searching. In the numerator of the above formula, maybe
instead of the criteria just "" also have 'not ERROR' or something like that.
any ideas how to specify that?
thanks