SUM Formula clearly sums the incorrect amount.

[Batman1]

I am adding nearly 1500 expenses throughout the 2012 year. The values were copy & pasted into excel from a website (to save me all the manual typing). Long story short in cell A1486 I enter the following formula =SUM(a1:a1483) I hit enter and the result is $8166.94. It is clearly wrong as there are nearly 1500 cells being added and the majority are over 100. The result should be a 6 digit figure ballpark around 200,000.

What I have done to troubleshoot:

I increased the digits after the decimal point, but since they were all copied & pasted they only extend to 2 decimal places then it is just zero's. So that means no rounding up or down?

I changed the cells to currency, but still no luck.

From reading online I have a suspicion it has something to do with copying & pasting & excel not recognizing some of the cells as values.


I attached a file so you can experiment with it. I've been cracking my head on this for a while then I came across excelforum.com

Sorry if this has been asked or posted somewhere else, I did a search, didn't find a similar forum.

Thank you all for your help. If someone finds the answer and would be happy to point out the mistake it would be something I can definitely learn from and we can all be a litter wiser.

Batman1

[Tank997]

Sounds like you have numbers stored as text.

[martindwilson]

there are several things wrong you have numbers formatted as $ but others text as $100 also trailing character(160)
put
in b1
=IF(A1="",0,SUBSTITUTE(SUBSTITUTE(A1,"$",""),CHAR(160),""))+0 fill down
you can either just sum that
or copy/paste special over original as values then delete col b

[AZ-XL]

They were saved as text string. Dollar sighn was not about the format. Also there were extra spaces too. See the file for real numbers. Its sum is more than 200000

[martindwilson]

@ AZ-XL your formula ignores a581

[AZ-XL]

@ AZ-XL your formula ignores a581

Yes you are right. Everything ok but a 581 there. Thank you

[Batman1]

Looks like my format was stored as text. martindwilson I used your formula =IF(A1="",0,SUBSTITUTE(SUBSTITUTE(A1,"$",""),CHAR(160),""))+0 fill down & it seems I solved the problem. Even though I'm not completely sure what the formula did. I summed a total of 218760.55 seems like the right figure.

You guys are Awesome! Also thank you all for your fast response!

Batman1