count number of occurances of a word in a range

I have a range that has different car models. Is it possible to have a
formula to count the number of occurances of a particular world.

e.g. range has ther following data Panda Active, Panda Dynamic, Punto
Active, Punto Dynamic, Punto Eleganza etc. Is it possible to have a formula
that will return the number of times "Punto" is listed in the range, or
"Dynamic" is listed in the range.

Thanks in advance for any help

Regards
John

Try something like this:

This formula counts the number of times the text in Cell A1 occurs in D1:D10

=SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)

Example:
A1: Sugar

D1: Sugar and Spice
D2: Sugar Bears contain no Sugar.

Count of Sugar is 3.
Note: SUBSTITUTE is case sensitive, hence the UPPER function.

Change range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"John Davies" wrote:

> I have a range that has different car models. Is it possible to have a
> formula to count the number of occurances of a particular world.
>
> e.g. range has ther following data Panda Active, Panda Dynamic, Punto
> Active, Punto Dynamic, Punto Eleganza etc. Is it possible to have a formula
> that will return the number of times "Punto" is listed in the range, or
> "Dynamic" is listed in the range.
>
> Thanks in advance for any help
>
> Regards
> John

Thanks for your help

This almost does the job, however on some lines e.g. I have Stilo 3 door
Saloon, Stilo 5 Door Saloon and Stilo Multiwagon. How can I get it to
differentiate between different Stilo models?

Thanks

"Ron Coderre" wrote:

> Try something like this:
>
> This formula counts the number of times the text in Cell A1 occurs in D1:D10
>
> =SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)
>
> Example:
> A1: Sugar
>
> D1: Sugar and Spice
> D2: Sugar Bears contain no Sugar.
>
> Count of Sugar is 3.
> Note: SUBSTITUTE is case sensitive, hence the UPPER function.
>
> Change range references to suit your situation.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "John Davies" wrote:
>
> > I have a range that has different car models. Is it possible to have a
> > formula to count the number of occurances of a particular world.
> >
> > e.g. range has ther following data Panda Active, Panda Dynamic, Punto
> > Active, Punto Dynamic, Punto Eleganza etc. Is it possible to have a formula
> > that will return the number of times "Punto" is listed in the range, or
> > "Dynamic" is listed in the range.
> >
> > Thanks in advance for any help
> >
> > Regards
> > John

If I understand you correctly, you don't want to count the occurrences of a
word in the range. You want to count the occurrences of a model in the range.

See if this is something you can use:

This one returns the count of cells that contain "Stilo 4 door saloon":
A1: Stilo 4 door saloon
B1: =COUNTIF(D1:D10,A1)

This formula counts the number of cells that start with "Stilo " and end
with " door saloon":
A1: Stilo * door saloon
B1: =COUNTIF(D1:D10,A1)

It would count:
Stilo 4 door saloon
Stilo with no door saloon
Stilo green with 2 door saloon

Note: The asterisk (*) is a wildcard that matches multiple characters. A
question mark (?) would match any single character.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"John Davies" wrote:

> Thanks for your help
>
> This almost does the job, however on some lines e.g. I have Stilo 3 door
> Saloon, Stilo 5 Door Saloon and Stilo Multiwagon. How can I get it to
> differentiate between different Stilo models?
>
> Thanks
>
> "Ron Coderre" wrote:
>
> > Try something like this:
> >
> > This formula counts the number of times the text in Cell A1 occurs in D1:D10
> >
> > =SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)
> >
> > Example:
> > A1: Sugar
> >
> > D1: Sugar and Spice
> > D2: Sugar Bears contain no Sugar.
> >
> > Count of Sugar is 3.
> > Note: SUBSTITUTE is case sensitive, hence the UPPER function.
> >
> > Change range references to suit your situation.
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "John Davies" wrote:
> >
> > > I have a range that has different car models. Is it possible to have a
> > > formula to count the number of occurances of a particular world.
> > >
> > > e.g. range has ther following data Panda Active, Panda Dynamic, Punto
> > > Active, Punto Dynamic, Punto Eleganza etc. Is it possible to have a formula
> > > that will return the number of times "Punto" is listed in the range, or
> > > "Dynamic" is listed in the range.
> > >
> > > Thanks in advance for any help
> > >
> > > Regards
> > > John