Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Count Unique Values Based on Multiple Criteria in Excel?
You'll appreciate the usefulness of being able to count unique values depending on various circumstances if you've ever needed to extract specific information from a large dataset. Excel's robust capabilities enable you to accomplish precisely that.
To provide a thorough knowledge of the ideas, we'll use real-world examples and step-by-step directions throughout this tutorial. By the end of this session, you'll have the knowledge and abilities necessary to competently handle any Excel data analysis work that involves counting distinct numbers depending on a variety of criteria. So, let's get started and unleash Excel's potential to effectively extract insightful data from your datasets!
Count Unique Values Based on Multiple Criteria
Here we will first count the values based on different criteria to complete the task. So let us see a simple process to learn how you can count unique values based on multiple criteria in Excel.
Step 1
Consider an Excel sheet where the data in the sheet is similar to the below image.
First, to count the values based on a single criterion, click on an empty cell and enter the formula as =SUM(IF("Komal"=$C$2:$C$20, 1/(COUNTIFS($C$2:$C$20, "Komal", $A$2:$A$20, $A$2:$A$20)), 0)) and click Ctrl + Shift + Enter.
Empty Cell > Formula > Ctrl + Shift + Enter.
Step 2
Now to count the number of cells based on two criteria, click on an empty cell and enter the formula as
=SUM(IF(("Komal"=$C$2:$C$20)*($D$2:$D$20<=DATE(2023,9,30)*($D$2:$D$20>=DATE(2023,9,1))),1/COUNTIFS($C$2:$C$20,"Komal",$A$2:$A$20,$A$2:$A$20,$D$2:$D$20,"<="&DATE(2023,9,30),$D$2:$D$20,">="&DATE(2023,9,1)),0)) then press Ctrl + Shift + Enter.
Empty Cell > Formula > Ctrl + Shift + Enter.
This is how you can count unique values based on multiple criteria in Excel.
Conclusion
In this tutorial, we have used a simple process to learn how you can count unique values based on multiple criteria in Excel to highlight a particular set of data.
409 Views
