| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using sum with cells containing text
How can I sum cells containg text?
I have a column of distances: 1km, 2 km, 3 km and I'd like to tally the distances using a formula. Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using sum with cells containing text
Hi!
As long as the format is consistent: n<spacekm n<spacekm n<spacekm =SUMPRODUCT(--(SUBSTITUTE(A1:A3," km",""))) Biff "Blue_Cup" wrote in message ... How can I sum cells containg text? I have a column of distances: 1km, 2 km, 3 km and I'd like to tally the distances using a formula. Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using sum with cells containing text
"Blue_Cup" wrote:
How can I sum cells containg text? I have a column of distances: 1km, 2 km, 3 km and I'd like to tally the distances using a formula. This might suffice: Assuming data in A1:A3, put in say, B1: =SUMPRODUCT(--(SUBSTITUTE(A1:A3,"km",""))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using sum with cells containing text
Why do you use that setup, one rule of the thumb is not mix text and numbers
that need to be calculated. You can use a custom format and get the km in the same cells with but the values being numbers Having said that if there are only km invloved =SUMPRODUCT(--(0&SUBSTITUTE(A1:A10,"km",""))) -- Regards, Peo Sjoblom Nothwest Excel Solutions www.nwexcelsolutions.com remove ^^ from email "Blue_Cup" wrote in message ... How can I sum cells containg text? I have a column of distances: 1km, 2 km, 3 km and I'd like to tally the distances using a formula. Thanks! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using sum with cells containing text
As long as the format is consistent:
n<spacekm n<spacekm n<spacekm =SUMPRODUCT(--(SUBSTITUTE(A1:A3," km",""))) Think the formula works even if the format is n<spacekm nkm n<spacekm =SUMPRODUCT(--(SUBSTITUTE(A1:A3,"km",""))) if we put it as "km", instead of " km" (a subtle difference <g) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using sum with cells containing text
Thanks for the replies!
I've inserted that function and keep getting a #NUM! error. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using sum with cells containing text
"Peo Sjobom" wrote: Why do you use that setup, one rule of the thumb is not mix text and numbers that need to be calculated. You can use a custom format and get the km in the same cells with but the values being numbers Having said that if there are only km invloved =SUMPRODUCT(--(0&SUBSTITUTE(A1:A10,"km",""))) -- Regards, Peo Sjoblom Nothwest Excel Solutions www.nwexcelsolutions.com remove ^^ from email "Blue_Cup" wrote in message ... How can I sum cells containg text? I have a column of distances: 1km, 2 km, 3 km and I'd like to tally the distances using a formula. Thanks! Thank-you! That works! :-) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using sum with cells containing text
"Blue_Cup" wrote:
Thanks for the replies! I've inserted that function and keep getting a #NUM! error. No prob, but I got it to work here based on your sample data -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using sum with cells containing text
How do you get #NUM! from that formula?
Biff "Max" wrote in message ... "Blue_Cup" wrote: Thanks for the replies! I've inserted that function and keep getting a #NUM! error. No prob, but I got it to work here based on your sample data -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup on large text in cells | Excel Worksheet Functions | |||
| merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
| Text shown up in other cells everytime a text is entered in 1 cell | Excel Discussion (Misc queries) | |||
| Combining & formatting cells with text - Excel 2002 | Excel Discussion (Misc queries) | |||
| how do i add the same text after current text in multiple cells | Excel Discussion (Misc queries) | |||
Linear Mode
