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!
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 


There is a new function in Excel which will add all the values either positive or negative while ignoring NA's.
9 is used for sum while 6 is for ignoring all NA's. There are other options, as well, beside addition. For example, you can do product, standard deviation, and so on. More information on AGGREGATE here. 


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

