[SOLVED] Sum function returning 0.00 instead of total

Question

In an excel document I have previously had no issues with, any new SUM functions I use, even on existing data, return 0.00 rather than the correct total. I have ensured data is formatted as numbers not text and yet any new attempts at using SUM or manually adding (e.g. =A1+A2+A3...) return 0.00, even though old =SUM functions in the document work until clicked on, then subsequently returning 0.00 as well.

Does anyone know why this might be happening or how to resolve this issue?

Edit: Saw a mention of circular arguments on another forum and the issue ended up being that the =SUM formulas were automatically including the cell the formula was in as well, causing an error. Problem fixed now but odd it happened.

joeuser2004 · Answer

Fickle_Frog&nbsp;&nbsp;It would benefit us and you if you attached an example Excel file (click the "browse" link near the bottom of the reply window).&nbsp;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.&nbsp; I like box.net/files; others like dropbox.com.&nbsp; Note:&nbsp; onedrive.live.com seems to be broken today; do not use it.&nbsp;-----Fickle_Frog&nbsp; wrote:&nbsp; ``I have ensured data is formatted as numbers not text``&nbsp;The format of a cell per se does not matter.&nbsp; Use formulas of the form =ISTEXT(A1) to determine if the type of the cell value is text.&nbsp;However, you say that =A1+A2+A3... returns 0.00.&nbsp; If that is correct, that would indicate that A1, A2, A3 et al are not text.&nbsp; Otherwise, a formula of that form would return #VALUE.&nbsp;Confirm that Automatic Calculation mode is enabled.&nbsp;Also, confirm that there are no circular references in any cell in the workbook.&nbsp;Finally (unlikely), confirm that Worksheet Calculation mode is enabled.&nbsp; That is not easy to do.&nbsp; You would need to look at ActiveSheet.EnableCalculation in VBA.&nbsp;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).&nbsp;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 (!).

ok_gis_critters · Answer

@Fickle_FrogJoeUser2004&nbsp;Dang, blad nastid....SUM() isn't working&nbsp;&nbsp;Found some help elsewhere in the forums here.&nbsp; And then found these instructions:&nbsp;https://support.microsoft.com/en-us/office/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885&nbsp;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.&nbsp;Yeah.&nbsp; It was driving me bonkers too...&nbsp;&nbsp;

Forum Discussion

[SOLVED] Sum function returning 0.00 instead of total

2 Replies

Resources