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!

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!

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
---

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!

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
---

Using sum with cells containing text
 

Thanks for the replies!

I've inserted that function and keep getting a #NUM! error.

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! :-)

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
---

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
---