Sum with letter

[Kim via OfficeKB.com]

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!

 

[Jason Morin]

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

 

[Kim via 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

 

[Jason Morin]

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

 

[Aladin Akyurek]

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)

 

[Kim via OfficeKB.com]

Thanks everyone, got it to work!