Solved How to sum the dollar & cents in excel?

December 7, 2015 at 23:44:04
Specs: Windows 7
Example:-
A1 $12.66
B1 12 (Dollar)
C1 66 (Cent)

A2 $13.95
B2 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?

Report •


#1
December 8, 2015 at 07:30:45
Try this:

=(B1+B2)+(C1+C2)/100

For longer lists, this might make more sense:

=SUM(B1:B100)+SUM(C1:C100)/100

or

=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/100

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message 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
✔ Best Answer
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 61

These 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)*1

This 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 •

Ask Question