Sum with letter

Hi there,

This may not be possible, but I figured that I would ask anyways.

Is there a way to sum values that contain a letter? For example:
4T + 5T = 9T or even just 9 as the end result would be ok.

Any tips or suggestions would be appreciated.

Thanks!

--
Message posted via http://www.officekb.com

It's possible, but the formula really depends on the
standard format. Is it always some number followed by
a "T"? If so, try:

=SUMPRODUCT(SUBSTITUTE(A1:A10,"T","")+0)

More detail and examples are necessary if the data is
more complex than what I've described.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hi there,
>
>This may not be possible, but I figured that I would ask
anyways.
>
>Is there a way to sum values that contain a letter? For
example:
>4T + 5T = 9T or even just 9 as the end result would be
ok.
>
>Any tips or suggestions would be appreciated.
>
>Thanks!
>
>--
>Message posted via http://www.officekb.com
>.
>

Hi Jason,

Yes, that is always the format. I just tried entering the formula and I
get a Value Error. Any ideas?

Thanks,
Kim

--
Message posted via http://www.officekb.com

The error means you have some empty cells in A1:A10.
Remove the empty cells or use:

=SUM(IF(A1:A10<>"",SUBSTITUTE(A1:A10,"T","")+0,0))

This one is an array formula, which means insteand of
just pressing enter, confirm it with ctrl + shift +
enter. XL will automatically places {} around the formula.

Jason

>-----Original Message-----
>Hi Jason,
>
>Yes, that is always the format. I just tried entering
the formula and I
>get a Value Error. Any ideas?
>
>Thanks,
>Kim
>
>--
>Message posted via http://www.officekb.com
>.
>

Jason Morin wrote:
> The error means you have some empty cells in A1:A10.
> Remove the empty cells or use:
>
> =SUM(IF(A1:A10<>"",SUBSTITUTE(A1:A10,"T","")+0,0))
>
> This one is an array formula, which means insteand of
> just pressing enter, confirm it with ctrl + shift +
> enter. XL will automatically places {} around the formula.
[...]

No need to switch...

=SUMPRODUCT(SUBSTITUTE(0&A1:A10,"T","")+0)

Thanks everyone, got it to work!

--
Message posted via http://www.officekb.com