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.

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&"*")

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&"*")

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&"*")

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

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