Count specific words in a range

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a group of names:
Group = Bill, John, Robert, Jeff, Helen, Sarah, Kelly, Dana, Mark
I need to count if any of the names in the group appears in a range of
cells.
I thought about creating one worksheet with the names listed and then
using countif, but I'm just stumped.
Any ideas?
 
Steve,

Are you looking for instances of those names where the name is the entire
cell:

Bill

Or are you looking for those names withins strings:

Bill went to town.

?

HTH,
Bernie
MS Excel MVP
 
I have a group of names:
Group = Bill, John, Robert, Jeff, Helen, Sarah, Kelly, Dana, Mark
I need to count if any of the names in the group appears in a range of
cells.
Click to expand...
...

Unclear. Do you mean you want to check whether each of the names appears in the
range, or do you want to count how many times each of the names appears in the
range, or do you want to count how many cells in the range contain any of these
names? The array formula

=COUNTIF(Range,NameList)>0

returns True for names in the list that appear in the range, false for those
names that don't. The array formula

=COUNTIF(Range,NameList)

returns how many times each name appears in the range. The formula

=SUMPRODUCT(COUNTIF(Range,NameList))

returns the number of cells containing any of the names. The formula

=SUMPRODUCT(--(COUNTIF(Range,NameList)>0))

returns the number of distinct names that appear in the range.
 
Back
Top