Finding the number of times a word is used in a range of cells
 

I have a column of cells in Excel 2000 with place names in them, such as
Phoenix, Arizona; White Plains, New York; Carson City, Nevada; Dayton, Ohio;
etc. Each cell usually contains two words, city and state, separated by a
comma. Although sometimes it's more than two words if the city and/or state
is two words, such as Carson City or New York.

What function and syntax would I use to find the number of times, say,
Arizona is used in the range of cells?

Thanks.

Finding the number of times a word is used in a range of cells
 

Ray Hill wrote:
I have a column of cells in Excel 2000 with place names in them, such as
Phoenix, Arizona; White Plains, New York; Carson City, Nevada; Dayton, Ohio;
etc. Each cell usually contains two words, city and state, separated by a
comma. Although sometimes it's more than two words if the city and/or state
is two words, such as Carson City or New York.

What function and syntax would I use to find the number of times, say,
Arizona is used in the range of cells?

Thanks.



Assuming your data is in A1:A100 and "Arizona" is in B1, put this in C1:

=COUNTIF(A1:A100,"*"&B1&"*")

Finding the number of times a word is used in a range of cells
 

To keep it simple, let's say my data is in cells L8 through L100. Say I want
to find the number of times Arizona is used in those cells. Each cell has at
least two words in it. What do I say?

"Glenn" wrote in message
...
Ray Hill wrote:
I have a column of cells in Excel 2000 with place names in them, such as
Phoenix, Arizona; White Plains, New York; Carson City, Nevada; Dayton,
Ohio; etc. Each cell usually contains two words, city and state,
separated by a comma. Although sometimes it's more than two words if the
city and/or state is two words, such as Carson City or New York.

What function and syntax would I use to find the number of times, say,
Arizona is used in the range of cells?

Thanks.

Assuming your data is in A1:A100 and "Arizona" is in B1, put this in C1:

=COUNTIF(A1:A100,"*"&B1&"*")

Finding the number of times a word is used in a range of cells
 

Actually I solved my own problem. Using the example below the syntax is:

=COUNTIF(L8:L100,"*Arizona*")

Thanks for the effort.

"Ray Hill" wrote in message
...
To keep it simple, let's say my data is in cells L8 through L100. Say I
want to find the number of times Arizona is used in those cells. Each cell
has at least two words in it. What do I say?

"Glenn" wrote in message
...
Ray Hill wrote:
I have a column of cells in Excel 2000 with place names in them, such as
Phoenix, Arizona; White Plains, New York; Carson City, Nevada; Dayton,
Ohio; etc. Each cell usually contains two words, city and state,
separated by a comma. Although sometimes it's more than two words if the
city and/or state is two words, such as Carson City or New York.

What function and syntax would I use to find the number of times, say,
Arizona is used in the range of cells?

Thanks.

Assuming your data is in A1:A100 and "Arizona" is in B1, put this in C1:

=COUNTIF(A1:A100,"*"&B1&"*")

Finding the number of times a word is used in a range of cells
 

One thing to keep in mind about using COUNTIF with the * wildcard is
that it doesn't actually count the instances of "Arizona" in L8:L100,
it only counts the cells with one or more instances of "Arizona". So,
if any of your cells have for example "Arizona Arizona" each such cell
will only add 1 to the count instead of 2.

If you need to count all the instances of "Arizona" in L8:L100 you
could use this array formula...
=SUM(LEN(L8:L100)-LEN(SUBSTITUTE(L8:L100,"Arizona","")))/LEN
("Arizona")

Since it is an array formula it needs to be entered using Ctrl+Shift
+Enter when added to the sheet as well as after each time has been
edited.

Ken Johnson

Finding the number of times a word is used in a range of cells
 

Thanks Ken. That's good to know. In my situation there is only one instance
of the word in each cell. But I'll file that bit of code away for future use
if I need it.

"Ken Johnson" wrote in message
...
One thing to keep in mind about using COUNTIF with the * wildcard is
that it doesn't actually count the instances of "Arizona" in L8:L100,
it only counts the cells with one or more instances of "Arizona". So,
if any of your cells have for example "Arizona Arizona" each such cell
will only add 1 to the count instead of 2.

If you need to count all the instances of "Arizona" in L8:L100 you
could use this array formula...
=SUM(LEN(L8:L100)-LEN(SUBSTITUTE(L8:L100,"Arizona","")))/LEN
("Arizona")

Since it is an array formula it needs to be entered using Ctrl+Shift
+Enter when added to the sheet as well as after each time has been
edited.

Ken Johnson

Finding the number of times a word is used in a range of cells
 

Hi Ray,

While you're at it add this to your files. I just discovered the same
can be achieved without having to use an array formula, thanks to
SUMPRODUCT.

=(SUMPRODUCT(LEN(L8:L100)-LEN(SUBSTITUTE(L8:L100,"Arizona","")))/LEN
("Arizona"))

Ken Johnson

Finding the number of times a word is used in a range of cells
 

Oops! just removed an unnecessary pair of parentheses...

=SUMPRODUCT(LEN(L8:L100)-LEN(SUBSTITUTE(L8:L100,"Arizona","")))/LEN
("Arizona")

Ken Johnson

Finding the number of times a word is used in a range of cells
 

Thanks Ken.

"Ken Johnson" wrote in message
...
Oops! just removed an unnecessary pair of parentheses...

=SUMPRODUCT(LEN(L8:L100)-LEN(SUBSTITUTE(L8:L100,"Arizona","")))/LEN
("Arizona")

Ken Johnson