sum function returns 0

wilydog

New Member
Joined
Feb 20, 2012
Messages
8
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!
 
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. :unsure:???
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top