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 or Numbers in a Column in Excel?
The robust spreadsheet programme Excel provides a variety of functions for data analysis and modification. Finding the number of distinct values or numbers in a given column is a typical task that can be helpful for many processes like data cleansing, deduplication, or statistical analysis.
In this article, we'll look at a variety of techniques for counting distinct values or numbers in Excel. This course will arm you with the skills you need to efficiently count unique values or numbers in your data, regardless of your level of Excel proficiency. Make sure Excel is installed on your computer before we start. The procedures and methods described in this lesson work with Excel 2010, 2013, 2016, 2019, and Excel 365, among other versions. So let's get started and learn the various Excel techniques for counting distinct values or numbers in a column!
Count Unique Values or Numbers in a Column
Here, we can complete the task using the formula directly. So let us see a simple process to learn how you can count unique values or numbers in a column in Excel.
Step 1
Consider an Excel sheet where the data in the sheet is similar to the below image.
First, count the unique numbers. Click on an empty cell, in this case cell C2, and enter the formula as =SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10)) and click enter. In the formula A1:A10, there is a range of numbers.
Empty Cell > Formula > Enter.
Step 2
Now to count the unique values with text, click on an empty cell and enter the formula as =SUM(IF(FREQUENCY(IF(LEN(C2:C10)>0,MATCH(C2:C10,C2:C10,0),""), IF(LEN(C2:C10)>0,MATCH(C2:C10,C2:C10,0),""))>0,1)) then click Ctrl + Shift + Enter.
Empty Cell > Formula > Ctrl + Shift + Enter.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can count unique values or numbers in a column in Excel to highlight a particular set of data.
153 Views
