Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of occurances of a word in a range
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of occurances of a word in a range
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
How do I count items within a date range in Excel? | Excel Worksheet Functions | |||
count duplicates/total number of occurances | Excel Discussion (Misc queries) | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
Formula to count number of days in range which are less than today | Excel Worksheet Functions |