Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Davies
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Davies
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
How do I count items within a date range in Excel? tcolbert Excel Worksheet Functions 2 January 9th 06 07:01 PM
count duplicates/total number of occurances Rishi Aggarwal Excel Discussion (Misc queries) 3 December 28th 05 05:15 AM
Count number of times a specific number is displayed in a cell ran subs Excel Worksheet Functions 1 June 27th 05 05:01 PM
Formula to count number of days in range which are less than today zooming Excel Worksheet Functions 2 June 21st 05 04:01 PM


All times are GMT +1. The time now is 12:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"