Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove/Ignore Letters when Summing cells in a column.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove/Ignore Letters when Summing cells in a column.
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. 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove/Ignore Letters when Summing cells in a column.
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 |
#4
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 |