How to #VALUE! = 0

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I'm referencing several cells in a formula, and the
scenario may arise that one of the cells being referenced
has the #VALUE! error. How can I make my formula
recognize it as 0 for SUM, or 1 for Product?

Scott
 
=SUMIF(A1:A3,"<>#VALUE!") instead of =SUM(A1:A3).

=PRODUCT(IF(ISERROR(A1:A3),1,A1:A3)) instead of =PRODUCT(A1:A3),

which must be confirmed with control+shift+enter, not just with enter.

It's of course better to prevent such an error in the range of interest.
 
Back
Top