How to #VALUE! = 0

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
 
A

Aladin Akyurek

=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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop down list control value in vba 2
Use TODAY() function as the criteria in a SUMIF formula 1
countifs Function not giving desired result with date criteria 2
Multiply Cell Values which include text units 6
Count Number of Unique Cells That Don't Have Certain Characters 0
Supress DIV/0 2
Blank Cells vs. Zero Values 2
Removing #N/A from an "If/Then" statement 4

Top