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,









"Matt" wrote:

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