sum function returns 0

[wilydog]

on an existing spreadsheet, the sum function is returning a 0 value. Cells that are being added together are formatted as numbers. No matter how the sum function is written, or a if working formula is copied to this cell, the answer is always 0. We have tried closing the sheet and re-opening. 2+2 must equal 4, not 0!

 

[Joe4]

What exactly does your SUM function look like?

What if you try to manually add two cells in your sum range, does that work?
i.e. = A1+B1

Also, if you use the ISNUMBER function on one of these cells, what does it return?
=ISNUMBER(A1)

Note that just because the cells are formatted a numbers does not necessarily mean that your entries are entered as numbers. For example, if you type in a single quote before a number, it will enter it as Text. Usually, a good indicator of this is if your entries are left justified instead of right justified, they are probably entered as Text.

If you highlight a column, and select Text to Columns from the Data menu, and then click Finish, this will change all those entries from Text to Numbers.

 

[Noodleski]

Your cells may be formatted as numbers, but are they really numeric?

Try to confirm by using the ISNUMBER function:
If your cells range from A1 to A5, fill cells B1 to B5 with isnumber(A1), isnumber(A2),...
It should return true or false. True => cells are numeric. Question remains
False => cells are text formatted as numbers.

 

[wilydog]

formula was entered using auto sum, also as "=sum()", and as "=A1+A2".

this has now been fixed by going to the Formulas tab, and under Calculation options switching from auto to manual.

 

[Joe4]

formula was entered using auto sum, also as "=sum()"

You probably know this, but you need a range between the parentheses in the SUM formula, i.e.
=SUM(A1:A10)

this has now been fixed by going to the Formulas tab, and under Calculation options switching from auto to manual.

This doesn't make sense to me. I could see an issue if it was set to Manual, and you changed it to Automatic, but going it the other way wouldn't correct a problem of a SUM not showing up. Automatic tells Excel to automatically recalculate anytime a change is made. Manual tells Excel not to, and the user must explicitly tell it to recalculate by pressing F9.

The only thing I can think of where switching from Automatic to Manual may be preferable in this situation if if your workbook contains so many calculations (i.e. literally "millions"), that Excel cannot track them all to do the Automatic calculation. Do you have a scenario like this, where you have millions of calculations in your workbook?

 

[wilydog]

this didn't make sense to us either. And there are not millions of calculations on the workbook. But, the spreadsheet works now. Thanks for your thoughts.

 

[Joe4]

Odd. I have to think that there is something else going on there.

 

[tsudanosh]

I realize this is an old thread but, since I spent the better part of two days dealing with this, I thought I would register and come here with my findings:

Some worksheets in the workbook were set to "EnableCalculation" = False.

This means, no matter what you do, it will always result in: 0

To check, and fix this, enable the Developer tab (if not already), find the offending worksheet(s) in the VBAProject -- then in the properties window, look for "EnableCalculation" and ensure it's set to "True".

This isn't something I had done on my own, but rather using a repository that has multiple users that interact with the workbook, so any other user could have gone in and done this (typically to speed it up if calculations aren't actually required at that time).

Hope this helps for some people... so they don't spend hours and hours banging their heads on a desk.

 

[D1XII]

on an existing spreadsheet, the sum function is returning a 0 value. Cells that are being added together are formatted as numbers. No matter how the sum function is written, or a if working formula is copied to this cell, the answer is always 0. We have tried closing the sheet and re-opening. 2+2 must equal 4, not 0!

I just had the same issue, and found this post.

The key was to make sure all of my subtotals (which also used sum functions) were proper. In so doing, I discovered one of my subtotals was incorrectly formatted. Fixing that solved my 0 return issue.

Hope this helps whoever sees this next.

 

[Habeas]

Check if your formula has circular references. Check all precedents also. For eg. K26+K27=0. Check K26. If K26=SUM(K15:K32) it would give you a zero because it involves a circular reference (K15:K32 includes K26, making the formula circular). Correcting K26 as K26=SUM(K15:K25) would give the correct formula. Also note that this could affect some other part of the work sheet. For eg if O26=K26+O25, this would also give 0 until the circular reference in K26 is removed. Hope this helps.

Note: In the above example, changing Calculation options to manual (in the formula tab) also works i.e. for some reason the circular reference is ignored when the manual option is turned on. But I think it is better to correct it this way rather than changing calculation options to manual.