# Using the SUM Function in Excel with N/A values

I am trying to create a formula for the Total cell at the bottom of a column. The problem is that some of the values in the blocks I am adding up are N/A values. (All the values in the blocks are also formulas themselves, with IF statements).

No matter what we have tried so far, we can't get the Total cell to calculate the total. We have tried the following:

• `SUM(IF(ISNA(A1:A4),0,A1:A4))`
• `SUMIF(A1:A4,"<>#N/A")`

Is it because the cells we are calculating a total for are formulas themselves? What are we missing?

• What happens with `=SUMIF(A1:A4,"<>#N/A")`? – Raystafarian Oct 3 '14 at 12:35
• Did you try entering the first formula as an array formula, i.e. with Ctrl+Shift+Enter? – Excellll Oct 3 '14 at 16:12

=SUM(SUMIF(A1:A4,{"<0",">0"}))

If all your values are non-negative then simply:

=SUMIF(A1:A4,">0")

Regards

• Neither of those worked either. :( – Lindi De Beer Oct 3 '14 at 12:37
• @LindiDeBeer, this formula definitely works. Did you copy and paste it exactly? – Mr. Mascaro Oct 3 '14 at 14:12
• Can you expand on "Niether of those worked....."? In what way, did you get zero as a result....or an error....or some other result that was incorrect? – barry houdini Oct 5 '14 at 20:51

I agree that XOR LX answer works and is probably the easiest solution.

An alternative would be

``````=SUM(IF(ISERROR(A1:A4),0,A1:A4))
``````

which is an array formula entered by pressing ctrl+shift+enter