Summing Numbers and Text in same cell

[Andrewba]

I am after trying to get a total for a range that includes numbers and text,
or a combination of the two.
Eg I need to add

A 5(h)
B 3(2h)
C 2(2s)
D 3(2H)

Total = 13
Total H = 5
Total S = 2

I have able to count the cells with H and S in, but need to return the total
numbers of H, and that of S.
The simple solution I know would be to have 2 seperate columns for the
number and the number of h's, that's easy, but not as interesting, or an
option at this time.
Any help towards this would be appreciated.

Thanks in advance

--b
AB120

 

[Ron Rosenfeld]

I am after trying to get a total for a range that includes numbers and text,
or a combination of the two.
Eg I need to add

A 5(h)
B 3(2h)
C 2(2s)
D 3(2H)

Total = 13
Total H = 5
Total S = 2

I have able to count the cells with H and S in, but need to return the total
numbers of H, and that of S.
The simple solution I know would be to have 2 seperate columns for the
number and the number of h's, that's easy, but not as interesting, or an
option at this time.
Any help towards this would be appreciated.

Thanks in advance

--b
AB120

If your formats are similar to what you show above, and especially if the value
preceding the h or s is only a single digit, then you can use these array
entered formulas: (to **array-enter** a formula, hold down <ctrl><shift> while
hitting <enter>. Excel will place braces {...} around the formula if you did
it correctly)

rng is the reference to your data area, and may include blanks.

Total:

=SUM(IF(ISNUMBER(-LEFT(rng,MATCH(FALSE,ISNUMBER(
-LEFT(rng,ROW(INDIRECT("1:10")))),0)-1)),--LEFT(rng,MATCH(
FALSE,ISNUMBER(-LEFT(rng,ROW(INDIRECT("1:10")))),0)-1),0))

Total H:
=SUM(IF(NOT(ISNUMBER(SEARCH("h",rng))),0,
IF(MID(rng,SEARCH("h",rng)-1,1)="(",1,
--MID(rng,SEARCH("h",rng)-1,1))))

Total S:
=SUM(IF(NOT(ISNUMBER(SEARCH("s",rng))),0,
IF(MID(rng,SEARCH("s",rng)-1,1)="(",1,
--MID(rng,SEARCH("s",rng)-1,1))))


You may find it more convenient to have a cell that contains "s" or "h"
(without the quote marks) and substitute a reference to that cell in the Total
H and Total S formulas.

If your formats are more complex, a VBA solution might be simpler, although it
could still be done with formulas.

--ron

 

[Andrewba]

Ron,

Many thanks, everything looks fine and dandy.

regards

 

[Ron Rosenfeld]

Ron,

Many thanks, everything looks fine and dandy.

regards

Glad to help. Thanks for the feedback.
--ron