How to Count Unique Values with Multiple Criteria in Excel?

With the help of the robust spreadsheet programme Excel, you can carry out a variety of computations and data analysis activities. It is frequently necessary to count the number of distinct values depending on various criteria while working with data. This tutorial will walk you through each step of accomplishing this task successfully. In many situations, counting unique values with different criteria can be quite helpful. You can quickly and simply get accurate results utilising Excel's features and functions without having to perform challenging manual computations.

Regardless of your level of Excel proficiency, this tutorial will arm you with the skills you need to efficiently analyse your data and draw insightful conclusions. Make sure you are comfortable with the fundamentals of Excel and its tools and formulae before we start. Make sure you also have a sample dataset on hand so you can follow the instruction. So let's get started and see how to use Excel to count unique values with numerous criteria!

Count Unique Values with Multiple Criteria

Here we will first remove the duplicate values, then count each value to complete the task. So let us see a simple process to learn how you can count unique values with multiple criteria in Excel.

Step 1

Consider an Excel sheet where you a have list of similar to the below image.

First, select the range of cells, then copy the cells to new location.

Then select new range of cells, click on Data, select Remove Duplicates, and click Ok.

Step 2

Then, to count every unique value, click on an empty cell and enter the formula as =COUNTIFS(A2:A14,$G2,B2:B14,$E$2,C2:C14,$F$2) and click enter. Then drag down using the autofill handle.

Empty Cell > Formula > Enter.

Step 3

Then drag down using the auto fill handle to complete the task.

This is how you can count unique values with multiple criteria in Excel.


In this tutorial, we have used a simple example to demonstrate how you can count unique values with multiple criteria in Excel to highlight a particular set of data.

Updated on: 22-Aug-2023

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started