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!

 

[Oberon]

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 had the SAME PROBLEM adding up external data. The data was formatted as numbers but was ignoring the formatting.

The simple solution I found was to wrap the whole thing in a VALUE().

i.e. =VALUE(HLOOKUP('Data'!A1-A2,'Data'!F5:R100, A1))

It was a simple solution that took me 2 hours to come up with. ???

 

[BarnOwl]

Just had this problem too, I thought I was doing some weird formatting mistake because I normally use MATLAB but no all the numbers were properly formatted and the SUM function steadfastly returned 0 despite there being about 20 non zero entries in the list of roughly 200 numbers.

I don't understand how there could be a circular reference in such a simple list but changing from automatic to manual calculation also fixed the problem for me

 

[BarnOwl]

Just had this problem too, I thought I was doing some weird formatting mistake because I normally use MATLAB but no all the numbers were properly formatted and the SUM function steadfastly returned 0 despite there being about 20 non zero entries in the list of roughly 200 numbers.

I don't understand how there could be a circular reference in such a simple list but changing from automatic to manual calculation also fixed the problem for me

Update there was a circular reference, I accidentally clicked something in a way to set one of the cells that read 0 to be equal to itself

 

[D1XII]

Update there was a circular reference, I accidentally clicked something in a way to set one of the cells that read 0 to be equal to itself

Glad it's solved. Really grateful for this thread - it's gotten me out of a lot of jams. I'm pretty convinced there's not an error in excel that hasn't been solved online already.

 

[Gia Energia]

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!

Your post was in 2012. Now we are in 2022. )) My error was that I was putting a comma instead of a dot to numbers when inserting manually. I was doing a sheet to keep track of a bank balance. I was putting: 12,34€ when instead, I should have written: 12.34 enter. It´s been a long time since I´ve worked with a panel. I was used to 365 Excel and now this Google Sheets, I thought it was the problem but instead, it was me all along.