How to Count Matching Values in Excel Columns?

If you are working with Excel, you may find it necessary to count the number of matching values in matching columns. This can be a time-consuming process if done manually, but fortunately, Excel provides several built-in functions that make this task much easier. In this article, we will explore how to count matching values in Excel columns, step-by-step.


Before we dive into the solution, let’s first understand the problem we are trying to solve. Imagine you have two columns in an Excel sheet, and you want to find out how many times the same value appears in both columns. For instance, you may have a list of employees in column A and a list of departments in column B. You want to know how many employees belong to each department.


There are several methods to count matching values in Excel, but we will focus on two commonly used techniques: using the COUNTIF function and using the PivotTable feature.

Using the COUNTIF Function

The COUNTIF function is a built-in Excel function that counts the number of cells in a range that meet a specified criterion. In our case, we want to count the number of cells in column A that match the cells in column B.

The formula should look something like this: 



Let’s use the example we mentioned earlier to see how these methods work in practice. Assume we have the following data in columns A and B:


We want to count how many times each employee appears in each department. Using the COUNTIF function, we can create the following formula in cell C2: 


We can then copy the formula down to apply it to other cells. This will give us the following results:


Using the PivotTable Feature

Another way to count matching values in Excel is by using the PivotTable feature. PivotTables are useful for summarizing large amounts of data quickly and easily.

Here are the steps to follow:

  • Select both columns of data.
  • Go to the “Insert” tab and click “PivotTable.”
  • Choose where you want the PivotTable to be placed and click “OK.”
  • Drag the column header you want to count to the “Values” area of the PivotTable.
  • Excel will automatically count the number of occurrences of each value in the selected column.
  • You can also add other fields to the PivotTable to further analyze the data.

Remember, the key to mastering Excel functions and formulas is practice, so don’t be afraid to experiment with different methods to see which one works best for your specific needs. With a little bit of patience and perseverance, you can become an Excel expert in no time!

Photo of author

Bibek Sapkota

I'm Bibek | Tech Enthusiast & Lifelong Learner. | Playing on the Web for the Past Few Years as an SEO Specialist and Full-Time Blogger. I'm constantly seeking out new opportunities to learn and grow, and I love sharing my knowledge with others. This is where I started this blog! Here, you will find me sharing comprehensive reviews, helpful guides, tips-tricks and ways to get the full benefits of evolving technology. On this blog, you can also explore Powerful Knowledge, Tips & Resources On Blogging, SEO and Passive income Opportunities.

Leave a Comment