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

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History