Recently I was given a large spreadsheet with people’s expertise written in long phrases, and I had to identify those people who have AI-related expertise. The issue here is that people can enter a variety of phrases such as “machine learning”, or “large language model”. How do I quickly identify those people?
Fortunately we can use the simple COUNTIF function, but with a twist. Here are the steps:
1- Build a list of keywords. Set them up in a column (or more precisely, in an array);
2- Use COUNTIF with the criteria set on the array. We also use the “*” symbol in the front and back of the phrases so Excel can search for those keyword within the texts;
3- Because Excel will return 1 and 0 in the form of the criteria array, we use a sum function to add them up into a single number.
That’s it. It seems simple, but it took me a while to figure that out. Initially I thought I was going to have to resort to some fancy tricks or functions such as VLOOKUP. But it was satisfying to find out a simple function will do. Simple is best.