count number of occurances of a word in a range
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
|