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?

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

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"?

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.

Thank you very much!

It does help me to solve the calculation issue!

Ask Your Question

Weekly Poll