How to use the COUNTIF Function Instead of VLOOKUP + Video - Excel Campus
12

How to use the COUNTIF Function Instead of VLOOKUP + Video

Bottom line: Learn how to use the COUNTIF function instead of VLOOKUP to determine if a value exists in a list or range.

Skill level: Beginner

COUNTIF Function Explained - Do Names Exist in a List or Range

Problem – I want to check and see if the names in column B exist in this long list of names in column E.

There are a lot of different ways we can solve this problem with formulas in Excel. You might be inclined to use a VLOOKUP formula, and that would work just fine.

However, there are a few advantages to using the COUNTIF function instead.

  1. It’s faster
  2. Easier
  3. And less prone to errors.

So let’s learn how COUNTIF works.

Video Tutorial on How to Use COUNTIF

Here is a video from The Ultimate Lookup Formulas Course that explains how to use COUNTIF instead of VLOOKUP.

*Double-click the video to watch full screen.

There are over 100 short videos just like the one above included in the Ultimate Lookup Formulas Course.

This course has been designed to help you master Excel’s most important functions and formulas in an easy step-by-step manner.

Click Here to Learn More About the Ultimate Lookup Formulas Course

Download the Example File

Download the Excel file to follow along.

COUNTIF Function - Check If A Value Exists.xlsx (18.5 KB)

The COUNTIF Function Explained

COUNTIF is a very simple function. It’s job is to search a range for a value and tell you how many cells match that value.

In the example below I am using a COUNTIF function to return the number of times the word “Surf” exists in the range.  The result is 3.

COUNTIF Function Explained - Counts the Number of Cells that Contain a Matching Value in a Range

Arguments: COUNTIF only has two arguments (Range, Criteria).

  1. Range – this is the range we want to search for the lookup value (Criteria).
  2. Criteria – this is the value we want to find.

Rules:  By default COUNTIF is going to look for an exact match of each cell’s entire value. You can add wild cards and conditional/relational operators to the criteria to look for partial matches.

Result: COUNTIF returns a numerical value for the number of cells that matched the criteria in the range.  If the COUNTIF returns a number greater than or equal to 1, then that means the value exists in the list.  If COUNTIF returns a zero then there are no cells that match the criteria.

So back to our original problem, we can use COUNTIF to see how many times the names exist in the list.

COUNTIF Function Explained - How Many Cells Contain the Name or Criteria

The cells in C2:C4 contain the COUNTIF function.

When COUNTIF returns a zero (0) this means the value does not exist.  So the result in cell C4 tells us that the name Bob Stevens does not exist in the list.

Why Use COUNTIF Instead of VLOOKUP?

When you just want to determine if a value exists in a list then I recommend using COUNTIF over VLOOKUP. It has a few advantages that make it more efficient, and also give you more insight to your data.

Here are 3 reasons to use COUNTIF instead of VLOOKUP (when you just want to see if a value exists in a range of cells):

  1. The COUNTIF function only has two arguments making it really fast and easy to write the formula.  VLOOKUP has four arguments.
  2. COUNTIF returns the total number of matching values in the range, so you can see if there is more than one matching value. VLOOKUP cannot do this, it only returns the first match.
  3. If the value does not exist, COUNTIF will return a zero (0). You do not need to worry about a formula error. With VLOOKUP, the formula would return an error and you would use and error handling function like IFERROR to handle the error.

Here is a comparison table of the list above, just in case your boss asks why… 🙂

COUNTIF vs VLOOKUP Table for Checking if a Value Exists

Checkout my article on VLOOKUP Explained at Starbucks if you want to learn more about how the function works.

COUNTIF vs VLOOKUP for Determining If A Value Exists

Here is an example of solving the same problem with COUNTIF vs VLOOKUP.

COUNTIF vs VLOOKUP Excel Example

The COUNTIF returns a number greater than or equal to 1 if the value exists in the list. It returns a zero if the value does not exist.

The VLOOKUP formula is going to return the matching value from the list. VLOOKUP returns a #N/A error if it can’t find the value in the list.

Obviously the VLOOKUP formula is longer, more complex, and will take more time to write.

Don’t Forget About COUNTIFS

The COUNTIF function has a big brother named COUNTIFS. This function allows you to specify multiple criteria in multiple ranges. You could use it to answer the question of how many Bob’s are in the East region from the data set below.

COUNTIFS Function to Check if Multiple Criteria Exist in Ranges

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …)

You can add up to 127 criteria!

You can also use COUNTIFS with only one criteria, just like you do with COUNTIF.

That means you could use COUNTIFS all the time instead of COUNTIF. I know a lot of people do this and that is perfectly fine.  I wanted to keep it simple by explaining COUNTIF first.

So now you know two new functions that you can use instead of VLOOKUP to find a matching value!

Please leave a comment below with any questions.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 12 comments
simon - September 14, 2017

Hello, great site so congrats for that, very helpful
I have used countifs to know how many securities match certain criterias such as spread, maturity etc
However while it does give me a number, i am helpless to design a fomrula allowing me to identify which securities are a match and counted by the Countifs formula
Is there an easy way to get the cells that are a match on top of just their number?
Thanks a lot

Reply
Robert Cunningham Madrigal - November 13, 2016

I need to know can I do to use the countif function using the vlookup to select different range with the same criteria, for instance: =countif(vlookup(A1,data,2,false),”X”).
This way I will be able to count the same criteria in different range with the need to create a formula for each one of the ranges.
I tried to do use it this way but it gives me an error, can you help me please, thanks in advance.

Reply
    Jon Acampora - November 21, 2016

    Hi Robert,

    Great question! You can use the INDEX function for this. INDEX will return an array and you can specify the column number you want to look at. The formula will look something like the following.

    =COUNTIF(INDEX(data,,1),”X”)

    The 1 represents the column number, which is the 1st column of the “data” range. If you changed that to 2 it would look in the 2nd column. You can also change the 1 to reference a cell that contains the number. Or you can use the MATCH function if you want to lookup the column header name to return the column number back to the INDEX function.

    I also have a free video series on the lookup formulas that covers VLOOKUP, INDEX/MATCH, and more.

    I hope that helps.

    Reply
recep baş - August 11, 2016

Thanks Jon. That is helpful.

Reply
Colin - January 17, 2016

Hi. In the past, it has always been necessary to ensure that the the two criteria ranges when using =COUNTIFS have to be of exactly the same number of rows. Is this still the case now?
Regards,
Colin

Reply
    Jon Acampora - January 22, 2016

    Hi Colin,
    Yes that is still the case. Your criteria ranges will need to be the same number of rows or columns. They don’t necessarily need to start and end at the same row or column, but they do need to contain the same number of rows or columns. Let me know if you have any questions. Thanks!

    Reply
      Aimee - February 10, 2016

      Is it possible to use COUNTIFS with the same range and just multi[le criteria?
      Thanks

      Reply
        Jon Acampora - February 10, 2016

        Great question Aimee. You can use COUNTIFS for multiple criteria in the same range, but it takes a few extra steps to make it work. There are basically two options.
        1. You can use two separate COUNTIFS functions and add them together. =COUNTIFS(criteria1) + COUNTIFS(criteria2). This would count each criteria on the range individually then sum them up.
        2. You can wrap the COUNTIFS in a SUM formula and add both criteria in curly brackets to create an array. =SUM(COUNTIFS(criteria_range, {criteria1,criteria2})

        Here is an article that explains both solutions in more detail.
        http://excelxor.com/2014/09/28/countifs-multiple-or-criteria-for-one-or-two-criteria_ranges/
        Thanks!

        Reply
Rahul - January 13, 2016

Really helpful.God bless

Reply
Sastry - January 13, 2016

Hi Jon,

This is excellent. I will use COUNTIFS instead of COUNTIF and replace VLOOKUP wherever possible. Great help.

Reply

Leave a Reply:

FREE Live Webinar 

The 5 Secrets to Understanding Pivot Tables
x

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!
x