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