count number of occurances of a word in a range

J

John Davies

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
 
R

Ron Coderre

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
 
J

John Davies

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
 
R

Ron Coderre

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
 
Top