| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove/Ignore Letters when Summing cells in a column.
=SUMPRODUCT(--SUBSTITUTE(SUBSTITUTE(A1:A6,"mi",""),"n/a",0))
"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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summing all numeric values only in cells in a column | Excel Worksheet Functions | |||
| Using SUM function to count hours BUT ignore Letters? | Excel Worksheet Functions | |||
| Remove Row numbers and Column letters from a specifice workbook | Excel Worksheet Functions | |||
| Remove dashes between letters and between letters and digits | Excel Worksheet Functions | |||
| How do i ignore letters in a formula? | Excel Worksheet Functions | |||
Hybrid Mode
