Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to find missing values with COUNTIF

Tags 
Summary 
In this video, we use COUNTIF to compare lists and count values that belong to both lists. We also "flip" the formula to count values in one list that are missing on the other.
Video Transcript 

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.

=COUNTIF($F$5:$F$1008,B5)

When I copy this formula down, we get a count for each name.

A "one" means the name appears once in the larger list. A zero means the name was not found.

Now if I select this column, the status bar shows a total count of names that appear in both lists.

Now let's reverse the logic - let's count names that don't appear in the big list.

A simple way to do this is to wrap an IF statement around the existing COUNTIF formula, and then just test the count.

=IF(COUNTIF($F$5:$F$1008,B5)=0,1,0)

If the count is zero, return one. Otherwise, return zero. This effectively "flips" the results.

When I copy the new formula down the column, we have a count of names that don't appear in the bigger list.

This problem really shows off the flexibility and utility of the COUNTIF function.

It's also a good example of how to nest one formula inside another to extend or change behavior.

Author 
Dave Bruns

Related shortcuts

Thanks a lot, this is perfectly working and saving me hours. - Sven
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course