In this worksheet, we have a list of the Wimbledon Men’s Singles Champions since 1968, in a table called “data”. How can we list names that appear more than once, along with a count? To start off, let’s look at how we might count the names in the data with the COUNTIF function in column H. For range, we want all the names in the source data. Now, for criteria, we want exactly the same list. In other words, we want COUNTIF to give us a count for each name in the list, using each name as a condition. When I enter the formula, we get a list of counts exactly as long as the names in the list. As you might guess, we can use a list like this with the FILTER function. I’ll first copy the COUNTIF formula to the clipboard, then I’ll delete the formula. In cell G5, I’ll enter the FILTER function. For array, we want the full list of names. For the include argument, we want to use the counts we saw earlier, so I’ll paste in the COUNTIF formula, then add greater than or equal to the number 2. When I enter the formula, we see only names that appear at least twice in the data. Now, we really want each name listed once, so I’ll wrap the UNIQUE function around FILTER to get rid of the extra names. Now let’s generate a count for each name in the list, again using the COUNTIF function. As before, range is the full set of names in the source data. However, for criteria, we use the unique list of names in column G. Notice the hash character refers to the entire spill range. Now we have a count for each name. Finally, let’s adjust the FILTER function to use a variable count, instead of a hardcoded count. In cell H2, I’ll enter the number 2. Then I’ll modify the FILTER formula to use H2 instead of a hardcoded number. Now I can easily change the filter to show only names that appear at least 3 times, or even 5 times.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
