Excel continues to have SUM 0.00 even though all formula are correct

%3CLINGO-SUB%20id%3D%22lingo-sub-1540600%22%20slang%3D%22en-US%22%3EExcel%20continues%20to%20have%20SUM%200.00%20even%20though%20all%20formula%20are%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540600%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20%3DSUM%20continues%20to%20display%200.00%20when%20adding%20multiple%20cells%20with%20function%20totals%20of%20their%20own.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20column%20values%20each%20have%20cell%20D4%20with%20formula%20%3DSUM(C4*M4)%20to%20get%20a%20total%20of%20two%20entered%20values%20for%20the%20row.%20This%20value%20calculates%20correctly.%20I%20have%20this%20formula%20continuing%20for%20an%20entire%20page%20down%20287%20rows.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20get%20a%20grand%20total%20for%20all%20rows%20so%20I%20have%20a%20formula%20in%20D2%20that%20reads%20%3DSUM(D4%3AD287).%20The%20formula%20result%20reads%200.00.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChecking%20all%20formula%20and%20all%20read%20correct.%20None%20are%20text%20or%20anything%20other%20than%20a%20formula.%20I%20have%20even%20tried%20to%20do%20%3DD4%2BD5%2BD6%2B%20ect%20and%20I%20get%20the%20same%20result.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20formulas%20are%20correct%20as%20far%20as%20I%20can%20tell.%20I%20have%20set%20them%20to%20Accounting%2C%20Currency%2C%20and%20even%20Just%20Numbers%20and%20it%20does%20not%20change.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20open%20up%20the%20Formula%20Function%20Arguments%20it%20shows%20the%20correct%20results%20and%20total.%20Yet%20the%20D2%20cell%20continues%20to%20show%200.00.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20went%20back%20through%20it%20and%20did%20a%20%3DSUM%20but%20just%20selected%20the%20cells%20with%20values%20other%20than%200.00%20and%20it%20finally%20totaled%20correctly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20something%20is%20taking%20the%20SUM%20of%20the%20column%20and%20only%20counting%20the%200.00%20cells%20and%20not%20the%20value%20calculated%20cells%20and%20displaying%20that%20result.%20Either%20that%20or%20there%20is%20something%20in%20the%20formula%20that%20is%20doing%20it.%20But%20its%20a%20simple%20%3DSUM()%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20would%20be%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1540600%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540615%22%20slang%3D%22en-US%22%3ERE%3A%20Excel%20continues%20to%20have%20SUM%200.00%20even%20though%20all%20formula%20are%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540615%22%20slang%3D%22en-US%22%3EHello%2C%20Probably%20the%20numbers%20are%20texts%20Mark%20the%20column%20with%20the%20'numbers'%2C%20complete%20data%20text%20in%20columns.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542080%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20continues%20to%20have%20SUM%200.00%20even%20though%20all%20formula%20are%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542080%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737429%22%20target%3D%22_blank%22%3E%40R0bperry904%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20sample%20file%20would%20certainly%20be%20helpful!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542125%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20continues%20to%20have%20SUM%200.00%20even%20though%20all%20formula%20are%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737429%22%20target%3D%22_blank%22%3E%40R0bperry904%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20why.%20But%20try.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(D2%3AD5%26lt%3B%26gt%3B%220.0%22%2CSUM(D2%3AD5))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578692%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20continues%20to%20have%20SUM%200.00%20even%20though%20all%20formula%20are%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578692%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737429%22%20target%3D%22_blank%22%3E%40R0bperry904%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20have%20the%20same%20issue%20with%20a%20new%20install%20of%20Excel%20-%20I'm%20trying%20to%20figure%20out%20a%20better%20way%20to%20correct%20the%20issue%20also.%26nbsp%3B%20I%20found%203%20items%20in%20a%20list%20of%20about%2025%20items%20that%20were%20not%20adding%20in.%26nbsp%3B%20I%20highlighted%20all%20numbers%20and%20formatted%20for%20numbers%20but%20it%20still%20didn't%20work.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20a%20way%20to%20correct%20it%2C%20but%20isn't%20ideal%20-%20I%20went%20to%20an%20open%20area%20on%20the%20spreadsheet%20and%20formatted%20for%20a%20number%20%3CU%3Ebefore%20%3C%2FU%3Etyping%20in%20the%20same%20number%2C%20then%20copied%20and%20pasted%20it%20back%20-%20it%20worked.%26nbsp%3B%20So%2C%20I'm%20thinking%20that%20it%20needs%20to%20be%20formatted%20for%20a%20number%20before%20you%20add%20any%20numbers.%26nbsp%3B%20It%20appears%20that%20text%20is%20the%20common%20format%20(I'm%20trying%20to%20find%20out%20how%20to%20change%20that)%2C%20so%20changing%20it%20to%20number%20after%20it's%20entered%2C%20doesn't%20fix%20the%20problem.%26nbsp%3B%20Hope%20that%20helps%20and%20I%20didn't%20confuse%20you%20too%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578723%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20continues%20to%20have%20SUM%200.00%20even%20though%20all%20formula%20are%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578723%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737429%22%20target%3D%22_blank%22%3E%40R0bperry904%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EOne%20thought%20would%20be%20that%20maybe%20the%20%E2%82%AC%20or%20sign%20is%20entered%20instead%20of%20assigning%20it%20through%20the%20format.%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3EIn%20addition%2C%20there%20may%20be%20unprintable%20characters%20in%20the%20cells.%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThis%20would%20mean%20that%20all%20values%20would%20be%20text%20and%20so%20they%20could%20not%20be%20calculated%20with%20them.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3ENikolino%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20know%20that%20I%20don't%20know%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578826%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20continues%20to%20have%20SUM%200.00%20even%20though%20all%20formula%20are%20correct%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578826%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20any%20of%20your%20numbers%20are%20left%20aligned%2C%20they%20are%20probably%20text%20-%20you%20can't%20just%20click%20on%20them%20and%20change%20them%20to%20numbers%20so...%3C%2FP%3E%3CP%3EI%20did%20a%20search%20on%20Excel%20%3CU%3E%3CSTRONG%3E%22convert%20text%20to%20numbers%22%3C%2FSTRONG%3E%3C%2FU%3E%20-%20pro%20users%20probably%20already%20know%20this%20but%20as%20I%20mentioned%2C%20I'm%20using%20a%20new%20version%20-%20if%20you%20highlight%20the%20column%20of%20numbers%20that%20aren't%20adding%20up%20and%20then%20go%20to%20%22Data%22%2C%20click%20on%20%22Text%20to%20columns%22%20-%20click%20Next%2C%20click%20Next%2C%20click%20Finish%20-%20it%20will%20convert%20any%20text%20to%20numbers.%20Much%20easier%20than%20my%20last%20post!%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754920%22%20target%3D%22_blank%22%3E%40Barb1010%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

My =SUM continues to display 0.00 when adding multiple cells with function totals of their own. 

My column values each have cell D4 with formula =SUM(C4*M4) to get a total of two entered values for the row. This value calculates correctly. I have this formula continuing for an entire page down 287 rows. 

 

I want to get a grand total for all rows so I have a formula in D2 that reads =SUM(D4:D287). The formula result reads 0.00. 

 

Checking all formula and all read correct. None are text or anything other than a formula. I have even tried to do =D4+D5+D6+ ect and I get the same result. 

 

My formulas are correct as far as I can tell. I have set them to Accounting, Currency, and even Just Numbers and it does not change. 

 

When I open up the Formula Function Arguments it shows the correct results and total. Yet the D2 cell continues to show 0.00. 

 

I went back through it and did a =SUM but just selected the cells with values other than 0.00 and it finally totaled correctly. 

 

So something is taking the SUM of the column and only counting the 0.00 cells and not the value calculated cells and displaying that result. Either that or there is something in the formula that is doing it. But its a simple =SUM() formula.

 

Any ideas would be helpful.

 

6 Replies
Highlighted
Hello, Probably the numbers are texts Mark the column with the 'numbers', complete data text in columns.
Highlighted

Hello @R0bperry904,

 

A sample file would certainly be helpful!

Highlighted

@R0bperry904 

 

I don't know why. But try.

=IF(D2:D5<>"0.0",SUM(D2:D5))

 

Highlighted

@R0bperry904 

I'm have the same issue with a new install of Excel - I'm trying to figure out a better way to correct the issue also.  I found 3 items in a list of about 25 items that were not adding in.  I highlighted all numbers and formatted for numbers but it still didn't work. 

 

I found a way to correct it, but isn't ideal - I went to an open area on the spreadsheet and formatted for a number before typing in the same number, then copied and pasted it back - it worked.  So, I'm thinking that it needs to be formatted for a number before you add any numbers.  It appears that text is the common format (I'm trying to find out how to change that), so changing it to number after it's entered, doesn't fix the problem.  Hope that helps and I didn't confuse you too much!

Highlighted

@R0bperry904 

 

One thought would be that maybe the € or $ sign is entered instead of assigning it through the format.
In addition, there may be unprintable characters in the cells.

This would mean that all values would be text and so they could not be calculated with them.


Nikolino
I know that I don't know (Socrates)

Highlighted

If any of your numbers are left aligned, they are probably text - you can't just click on them and change them to numbers so...

I did a search on Excel "convert text to numbers" - pro users probably already know this but as I mentioned, I'm using a new version - if you highlight the column of numbers that aren't adding up and then go to "Data", click on "Text to columns" - click Next, click Next, click Finish - it will convert any text to numbers. Much easier than my last post!

@Barb1010