Forum Discussion
[SOLVED] Sum function returning 0.00 instead of total
It would benefit us and you if you attached an example Excel file (click the "browse" link near the bottom of the reply window).
If you cannot attach per se (some people say the forum won't allow them to), upload the Excel file to a file-sharing website, and post the download URL. I like box.net/files; others like dropbox.com. Note: onedrive.live.com seems to be broken today; do not use it.
-----
Fickle_Frog wrote: ``I have ensured data is formatted as numbers not text``
The format of a cell per se does not matter. Use formulas of the form =ISTEXT(A1) to determine if the type of the cell value is text.
However, you say that =A1+A2+A3... returns 0.00. If that is correct, that would indicate that A1, A2, A3 et al are not text. Otherwise, a formula of that form would return #VALUE.
Confirm that Automatic Calculation mode is enabled.
Also, confirm that there are no circular references in any cell in the workbook.
Finally (unlikely), confirm that Worksheet Calculation mode is enabled. That is not easy to do. You would need to look at ActiveSheet.EnableCalculation in VBA.
And look at the cell format to be sure that it is not literally "0.00" (with double-quotes) instead of 0.00 (format as Number with 2 decimal places).
Technically, a Custom format that is literally "0.00" (with double-quotes) is a numeric format that always displays 0.00 for any numeric value (!).
Dang, blad nastid....
SUM() isn't working
Found some help elsewhere in the forums here. And then found these instructions: https://support.microsoft.com/en-us/office/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885
Even if the values that were provided to fill in this year's columns look like numbers, are in cells that have been formatted as holding numbers, and seem to be numbers, there's still a chance that they're in fact text.
Yeah. It was driving me bonkers too...
