# Solved How to sum the dollar & cents in excel?

December 7, 2015 at 23:44:04
Specs: Windows 7
 Example:-A1 \$12.66B1 12 (Dollar)C1 66 (Cent)A2 \$13.95B2 13 (Dollar)C2 95 (Cent)If I want to sum the amount of "B1, B2, C1, C2", what formula should I use?I can't sum the as the dollar and cent are on different column.Anyone can help me?

See More: How to sum the dollar & cents in excel?

#1
December 8, 2015 at 07:30:45
 Try this:=(B1+B2)+(C1+C2)/100For longer lists, this might make more sense:=SUM(B1:B100)+SUM(C1:C100)/100or=SUM(SUM(B1:B2),SUM(C1:C2)/100)This will also work, based on the Order of Operations that Excel follows, but it is a bit cumbersome. I'm just tossing it out for general information.=B1+C1/100+B2+C2/100message edited by DerbyDad03

Report •

#2
December 8, 2015 at 23:57:29
 Thanks DerbyDad03!I managed to sum the amount based on your formula provided.What if the calculation need to be done according to dollar & cent column separately?Formula which I used as below:-Column "Dollar" : =sum(B1:B2) result equal to "25" (it should be 26)Column "Cent" : =sum(C1:C2)/100 result equal to "1.61" (it should be 61)Is there any formula can be used, so that whichever number >1 in column "Cent" will automatically add to column "Dollar"?

Report •

#3
December 9, 2015 at 12:04:01
 re: "Column "Dollar" : =sum(B1:B2) result equal to "25" (it should be 26)"No, it should be 25, you just want it to be 26.re: "Column "Cent" : =sum(C1:C2)/100 result equal to "1.61" (it should be 61)"No, it should be 1.61, you just want it to be 61These formulas should work for 2 cells:Dollars:=IF(SUM(C1:C2)/100>1,SUM(B1:B2,1),SUM(B1:B2))Cents:=IF(SUM(C1:C2)>100,SUM(C1:C2)-100,SUM(C1:C2))If want to SUM a longer list, then these will work for 2 or more cells:Dollars:=SUM(B1:B9)+INT(SUM(C1:C9)/100)This SUMS the Dollar cells and then adds in the integer portion of the SUM of the Cents cells.Cents:=RIGHT(SUM(C1:C9),2)*1This extracts the last 2 digits of the SUM of the Cents cells. However, the RIGHT function creates a Text string. To turn it back into a Number, we need to multiply the result by 1 (or add 0, etc.) Excel is smart enough to know that if you perform a mathematical operation on the Text representation of a Number, you must want a Number as the result.

Report •

Related Solutions

#4
December 9, 2015 at 21:31:51
 Thank you very much!It does help me to solve the calculation issue!

Report •