Sum function not working - returning zero

Last Edited By Krjb Donovan
Last Updated: May 23, 2014 08:13 PM GMT

SumIf Shows 0

I have been using the sum function for a very long time, but just recently got a new computer and the "new" Excel 2007. A few things have tripped me up here, and I'm hoping there's some magic button I haven't found yet to fix my problem.

I am having problems with a basic sum formula. I created the formula for one column and it worked great. Then I filled across and it worked great for the next two columns, but the last returned "zero" as an answer. I tried formatting the numbers as text in the cell formatting section. I tried copying the numbers and doing a "paste special" as a "value", but strangely that actually seemed to make the numbers text (lined up on the left - where the original ones lined up on the right).

Answer: Most Likely The Cells Contain Text

If the SUM is returning 0, then the data is most likely not all text or the cells being summed are not all formatted as numeric, or they actually coincidentally sum to zero.

In your sum formula:

  • Select the range. That is, in the formula =SUM(E1:E20), then select the cells "E1:E20"
  • then press the F9 key.

If you see quotes around the numbers, then they are text formatted cells. You may see ={"100";"150",...}

Fix: Reformat As General

You can fix this by doing the following:

  • reformat your range as General
  • then select an empty cell with also a general format
  • copy it, paste special it onto the range and use Values and Add.

You may need to press F9 afterwards in order to recalculate.

Advertisement

©2017 eLuminary LLC. All rights reserved.