Need to add figures based on alpha characters in the same cell
 

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

Need to add figures based on alpha characters in the same cell
 

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

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

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

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

Need to add figures based on alpha characters in the same cell
 

It worked - 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).

Need to add figures based on alpha characters in the same cell
 

I'm sorry, I wasn't able to make this work
"vezerid" wrote:

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

Need to add figures based on alpha characters in the same cell
 

Do you know how I can use your formula below in Google Docs(spreadsheets).
I keep getting an error. Google seems to have all the functions you used -
however I don't know what the -- means that you have just before the word LEFT

(The formula works great in Excel)


"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).

Need to add figures based on alpha characters in the same cell
 

I've never used the Google spreadsheet so I don't know how compatible it is
with Excel.

--
Biff
Microsoft Excel MVP


"Frustrated in Iowa" wrote in
message ...

Do you know how I can use your formula below in Google Docs(spreadsheets).
I keep getting an error. Google seems to have all the functions you
used -
however I don't know what the -- means that you have just before the word
LEFT

(The formula works great in Excel)


"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).

Need to add figures based on alpha characters in the same cell
 

could not get this to work, but do appreciate your help

"Mike H" wrote:

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

Need to add figures based on alpha characters in the same cell
 

I don't know what the -- means that you have
just before the word LEFT

LEFT is a *text* function so it returns a *text* value. We need to convert
the result of LEFT into numeric numbers that SUM can add together. SUM
ignores text.

The double unary (--) coerces the text numbers returned by LEFT into numeric
numbers which SUM can then add up.


--
Biff
Microsoft Excel MVP


"Frustrated in Iowa" wrote in
message ...

Do you know how I can use your formula below in Google Docs(spreadsheets).
I keep getting an error. Google seems to have all the functions you
used -
however I don't know what the -- means that you have just before the word
LEFT

(The formula works great in Excel)


"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).