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

On Fri, 8 Aug 2008 03:48:01 -0700, Andrewba
wrote:

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

Ron,

Many thanks, everything looks fine and dandy.

regards


--
AB120


"Ron Rosenfeld" wrote:

On Fri, 8 Aug 2008 03:48:01 -0700, Andrewba
wrote:

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

On Fri, 8 Aug 2008 06:17:01 -0700, Andrewba
wrote:

Ron,

Many thanks, everything looks fine and dandy.

regards


--
AB120


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