Remove/Ignore Letters when Summing cells in a column.

[Matt]

10+12+20+25+35I have a column of data that has number, as well as letters. I
need to add the numbers to get the sum. The column might look like:

10mi
12mi
20mi
n/a
25mi
35mi

The letters are always the last two characters in the cell. Some cells have
"n/a" which can be 0.
So, in the above example, the sum I am looking for is 102 (10+12+20+0+25+35).
How can i write the formula so that it either ignores letters, or truncates
the cell 2 characters.

Thanks

 

[Dave Peterson]

First, your life will be lots easier if you split your data into multiple
columns. One for the number and one for the unit of measure.

But if all the last two characters are "mi", then this worked ok for me:

=SUMPRODUCT(--("0"&(SUBSTITUTE(SUBSTITUTE(LOWER(A1:A10),"mi",""),"n/a",""))))

Change the range to match what you need, but you can't use the entire column
until xl2007.

 

[xlm]

if "mi" are the only characters to the right of the numbers,
try this array formula. confirm by Ctrl,Shift and Enter together

=SUM(IF(RIGHT(A1:A6,2)="mi",--LEFT(A1:A6,LEN(A1:A6)-2),0))

this will sum all numbers on the left of the string.

Does this do what you want?

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,

 

[Teethless mama]

=SUMPRODUCT(--SUBSTITUTE(SUBSTITUTE(A1:A6,"mi",""),"n/a",0))