Using sum with cells containing text

[Guest]

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!

 

[Biff]

Hi!

As long as the format is consistent:

n<space>km
n<space>km
n<space>km

=SUMPRODUCT(--(SUBSTITUTE(A1:A3," km","")))

Biff

 

[Max]

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","")))

---

 

[Peo Sjobom]

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

 

[Max]

As long as the format is consistent:

n<space>km
n<space>km
n<space>km

=SUMPRODUCT(--(SUBSTITUTE(A1:A3," km","")))

Think the formula works even if the format is

n<space>km
nkm
n<space>km

=SUMPRODUCT(--(SUBSTITUTE(A1:A3,"km","")))

if we put it as "km", instead of " km"
(a subtle difference <g>)

---

 

[Guest]

Thanks for the replies!

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

 

[Guest]

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

Thank-you! That works!

 

[Max]

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

---

 

[Biff]

How do you get #NUM! from that formula?

Biff

No prob, but I got it to work here based on your sample data