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 Same Or Duplicate Values Only Once In A Column?
When working with large datasets in Excel or any other spreadsheet software, it is common to come across a column containing multiple occurrences of the same value. However, in some cases, you may only be interested in counting the unique values and ignoring the duplicates. Counting unique values in a column can be a time-consuming task, especially when dealing with a large dataset. In this tutorial, we will explore different methods that you can use to count the same or duplicate values only once in a column. Whether you are a beginner or an advanced user, this tutorial will provide you with step-by-step instructions that you can follow to efficiently count unique values in Excel. So, let's get started!
Count Same Or Duplicate Values Only Once
Here, we will first count the duplicate values only once, without considering case sensitive, using a formula, and again use another formula to count by considering case sensitive. So let us see a simple process to know how you can count the same or duplicate values only once in a column.
Step 1
Consider an Excel sheet where you have a list of items similar to the below image.
Now to count the duplicates once without case-sensitivity, click on an empty cell in our case cell D2 and enter the formula as
=SUMPRODUCT((A2:A16<>"")/COUNTIF(A2:A16,A2:A16&"")) and click enter.
Empty cell > Formula > Enter.
Step 2
If you want to count duplicates once by considering case-sensitive, we can use the formula as
=SUM(IFERROR(1/IF(A2:A16<>"", FREQUENCY(IF(EXACT(A2:A16, TRANSPOSE(A2:A16)), MATCH(ROW(A2:A16), ROW(A2:A16)), ""), MATCH(ROW(A2:A16), ROW(A2:A16))), 0), 0))
Empty cell > Formula > Enter.
Conclusion
In this tutorial, we used a simple example to demonstrate how you can count the same or duplicate values only once in a column to highlight a particular set of data.
913 Views
