Using COUNTIFS() to find cells that contain specific words

Question

I am attempting to create a word frequency counter that will read through cells that contain more than 1 word. For instance, I have over 10,000 rows that all contain sentences and I would like to grab a count of all the cells that contain the word "unhappy." The problem is that I have an entire list of different words that I would like to search for, like:

     A        B
|  Words  | Count |
| unhappy |   1   |
| irate   |   3   | 
| angry   |   4   |

I've written the following:

=COUNTIF(A:A, "*" & "A2" & "*")

I'm trying to reference the A2 cell and then just autofill the other counts that correspond to the other words in the list. Any ideas?

Answer the question so that I can credit your solution. Thanks, btw! — Fullmetal_Alchemist_Fan

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-05-05T19:50:39

1

votes

As I mentioned in comments, you should use A2 without quotes:

=COUNTIF(A:A, "*" & A2 & "*")

Using COUNTIFS() to find cells that contain specific words

1 Answers