Need to add figures based on alpha characters in the same cell
Assume data in row 2, A2:K2.
Total Sales:
=SUMPRODUCT(--LEFT(A2:K2,LEN(A2:K2)-2))
If strings cc,ca,ch are in cells L1:N1 then you can calculate their
respective subtotals with the following formula:
=SUMPRODUCT(LEFT($A2:$K2,LEN($A2:$K2)-2)*(RIGHT($A2:$K2,2)=L$1))
This formula can be copied to the right and down to produce the
subtotals for each day.
HTH
Kostis Vezerides
On Nov 13, 9:01*pm, Frustrated in Iowa <Frustrated in
wrote:
My data is in the format of 55.45cc *34.00ca *23.21 ch ...... where the
number represents a dollar amount of a sale and the letters indicate
cc=credit card, ca=cash etc. *Each number/character pair is in a single cell.
*e.g. 55.45cc is in one cell. *A row of these numbers/characters *like above *
represent a day of sales. *I want to have totals for all credit cards, all
cash, and all check. *Hope this makes sense. *If not here is an example (I
have left out any numbers after the decimal)
10cc *5ca 20cc *5cc *10ca 5ca
In the above, the cc total should be $30 (i.e. 10cc + 20cc) and the ca total
should be $20 (i.e. 5ca + 10 ca + 5ca).
|