Thread
:
Need to add figures based on alpha characters in the same cell
View Single Post
#
5
Posted to microsoft.public.excel.worksheet.functions
Frustrated in Iowa[_2_]
external usenet poster
Posts: 5
Need to add figures based on alpha characters in the same cell
Ooops - you are right - it should be 35. I will try this out. Thanks a lot
"T. Valko" wrote:
10cc 5ca 20cc 5cc 10ca 5ca
In the above, the cc total should be $30 (i.e. 10cc + 20cc)
I think it should be 35. 10+20+5
Try this array formula** :
=SUM(IF(RIGHT(A1:F1,2)="cc",--LEFT(A1:F1,LEN(A1:F1)-2)))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"Frustrated in Iowa" <Frustrated in
wrote in
message ...
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).
Reply With Quote
Frustrated in Iowa[_2_]
View Public Profile
Find all posts by Frustrated in Iowa[_2_]