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

[Ray Hill]

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.

 

[Glenn]

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

 

[Ray Hill]

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?

 

[Ray Hill]

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

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

Thanks for the effort.

 

[Ken Johnson]

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

 

[Ray Hill]

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]

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

 

[Ken Johnson]

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

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

Ken Johnson

 

[Ray Hill]

Thanks Ken.