In this video, we'll take a look at how to use the COUNTIF function to solve a common problem: how to find values in one list that appear in another list. Or, how to find values in a list that don't appear in another list.
Let's take a look.
In this worksheet, on the left, I have a list of 20 names.
On the right, I have a much larger list of over 1000 names.
How can I quickly figure out which names in the smaller list also appear in the bigger list?
Well, there are several ways we can approach this with formulas in Excel. We could for example use the VLOOKUP function or the MATCH function to find exact matches.
But another powerful and simple option is the COUNTIF function.
To use the COUNTIF function, I need to indicate the range to look in, and the criteria to use when counting. The range is simply the list of names in the large list. I need to lock this reference so it won't change when the formula is copied.
For criteria, I simply use the name in the current row.