Using sum with cells containing text

[Blue_Cup]

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

"Blue_Cup" <[email protected]> wrote in message
news:[email protected]...
> 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!

[Max]

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

[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


"Blue_Cup" <[email protected]> wrote in message
news:[email protected]...
> 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!

[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>)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Blue_Cup]

Thanks for the replies!

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

[Blue_Cup]

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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! :-)

[Max]

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

[Biff]

How do you get #NUM! from that formula?

Biff

"Max" <[email protected]> wrote in message
news:[email protected]...
> "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
> ---
>
>