I have the following range A1:G44 and the values in cells:

A2 = 4B
A5 = 8L
B2 = 1L
A10 = 1B
C10 = 0.5L

I need to sum the L's and the B's separately so that in this case there
would be a total of 5Bs and 9.5Ls.

Can this be accomplished and if so, how?

Try this array formula**:

=SUM(IF(ISNUMBER(SEARCH("B",A1:G44)),--SUBSTITUTE(A1:G44,"B","")))

For the Ls, just replace the "B" in the formula.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Anna" wrote in message
...
I have the following range A1:G44 and the values in cells:

A2 = 4B
A5 = 8L
B2 = 1L
A10 = 1B
C10 = 0.5L

I need to sum the L's and the B's separately so that in this case there
would be a total of 5Bs and 9.5Ls.

Can this be accomplished and if so, how?

=SUM(IF(RIGHT(A1:C10,1)="B",LEFT(A1:C10,LEN(A1:C10 )-1)+0))

ctrl+shift+enter, not just enter


"Anna" wrote:

I have the following range A1:G44 and the values in cells:

A2 = 4B
A5 = 8L
B2 = 1L
A10 = 1B
C10 = 0.5L

I need to sum the L's and the B's separately so that in this case there
would be a total of 5Bs and 9.5Ls.

Can this be accomplished and if so, how?

Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just Enter:

=SUM(IF(RIGHT(A1:G44,1)="L",--SUBSTITUTE(A1:G44,"L","")))
=SUM(IF(RIGHT(A1:G44,1)="B",--SUBSTITUTE(A1:G44,"B","")))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Anna" wrote:

I have the following range A1:G44 and the values in cells:

A2 = 4B
A5 = 8L
B2 = 1L
A10 = 1B
C10 = 0.5L

I need to sum the L's and the B's separately so that in this case there
would be a total of 5Bs and 9.5Ls.

Can this be accomplished and if so, how?

Yes. It works like a charm. Thank you everyone for your help.

"Ron Coderre" wrote:

Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just Enter:

=SUM(IF(RIGHT(A1:G44,1)="L",--SUBSTITUTE(A1:G44,"L","")))
=SUM(IF(RIGHT(A1:G44,1)="B",--SUBSTITUTE(A1:G44,"B","")))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Anna" wrote:

I have the following range A1:G44 and the values in cells:

A2 = 4B
A5 = 8L
B2 = 1L
A10 = 1B
C10 = 0.5L

I need to sum the L's and the B's separately so that in this case there
would be a total of 5Bs and 9.5Ls.

Can this be accomplished and if so, how?

I'm glad I could help....thanks for letting me know.


***********
Regards,
Ron

XL2002, WinXP


"Anna" wrote:

Yes. It works like a charm. Thank you everyone for your help.

"Ron Coderre" wrote:

Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just Enter:

=SUM(IF(RIGHT(A1:G44,1)="L",--SUBSTITUTE(A1:G44,"L","")))
=SUM(IF(RIGHT(A1:G44,1)="B",--SUBSTITUTE(A1:G44,"B","")))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Anna" wrote:

I have the following range A1:G44 and the values in cells:

A2 = 4B
A5 = 8L
B2 = 1L
A10 = 1B
C10 = 0.5L

I need to sum the L's and the B's separately so that in this case there
would be a total of 5Bs and 9.5Ls.

Can this be accomplished and if so, how?