How can I count the number of times a word appears in Excel if it doesn't always appear by itself?

Question

I am putting together a database that shows the historic conference championships. Most of the time, one team wins it outright, so I can count how many they've won with a simple COUNTIF formula. However, sometimes teams are co-champions, so I will enter it for example as: Armstrong, Cooper. When I run COUNTIF on cells like this, it does not count them since the cell does not precisely equal "Armstrong" or "Cooper". Is there a formula I can run or any other manner by which I can circumvent this technicality?

Answer 1

Just use COUNTIF with wildcards in your search string, like this:
=COUNTIF( A1:A15 , "=*Armstrong*" )

If you want to compare against a value contained in a cell (e.g. B13):
=COUNTIF(A1:A15, CONCATENATE ("*",B13,"*"))