CODEWITHSUNDEEP
excelspreadsheet
Pen Daryn
Pen Daryn

Reputation: 3

Excel formula for counting the number of incidents of a word in a column?

Is there a quicker way of searching for terms without typing each one into the formula? Like, say I have a column that has a bunch of names of locations and I want to find out how many times each one comes up.

This is the formula for when I type in the locations: 

=COUNTIF($F$2:$F$274,"*AD library*")

I just modify the AD library to the next one, say monastery so it would be 

=COUNTIF($F$2:$F$274,"*monastery*")

Is there another way of getting the same info without having to type in each one (it's a big sheet with a lot of locations).

Thanks

Upvotes: 0

Views: 164

Answers (2)

Andreas
Andreas

Reputation: 23968

Go to the insert tab and insert a pivot table.

Then drag the header (locations?) you want the count of to row labels and any other header to the value field (preferably something with text).

Now the pivot should give you the count of each item.
If you don't have a column with text then choose any other and you need to switch from sum to count in the value field settings.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

use PivotTable on just that one column. Put that column in both rows and Values:

enter image description here

Upvotes: 1

Related Questions