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


If all your values are non-negative then simply:



  • 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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.