Need to add figures based on alpha characters in the same cell
Hi,
Try this
=SUM(IF(RIGHT(A1:A20,2)="cc",LEFT(A1:A20,5)*1))
In practice you could use a cell address of the "cc"
'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array
Mike
"Frustrated in Iowa" 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).
|