I have a range and need to sum it using SUM function but the result is N/A if there is any N/A value. How can I make the SUM function to treat the N/A value as 0 value? Please help!
Take the 2minute tour
×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
Use Array Formula
Press CTRL+SHIFT+ENTER after entering the formula Note: Replace A1:A4 with your range 


The easiest way: Use So the syntax for that one is
You will get the same result since it will ignore any nonnumeric or 0 values. 


Use SUBTOTAL instead:



You can convert the #N/A to zeros in your source range and then use the normal SUM function: =IF(ISNA(OriginalFormula), 0, OriginalFormula) 


If there are formulas I'd copy as numbers, replace #N/A in column with 0 and then sum as usually 


This way it seems to work:
Not so robust but effective! 

