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 with Blanks in an Excel Column?
Excel is a robust data management and analysis tool, and the ability to count unique values is a crucial skill for Excel users. Despite the presence of blank cells, we will walk you through the process of counting unique values in a column in this tutorial. In data analysis, counting unique values is a typical activity that becomes even more crucial when working with huge datasets that could contain empty cells. We will thoroughly examine the functions and methods that Excel provides to tackle this situation.
We will presume that you are familiar with Excel's interface and have a basic comprehension of the programme throughout this course. Even if you are a newbie, the directions will be simple to understand. You will have the knowledge and abilities necessary to precisely count unique values in any Excel column, including blanks, after completing this training. Let's begin this fascinating trip of discovering how to count distinct values in an Excel column that contains blanks!
Count Unique Values with Blanks
Here, we can complete the task using the formula directly. So let us see a simple process to know how you can count unique values with blanks in an Excel column.
Consider an Excel sheet where you have a list of values with some blanks and duplicate values, similar to the below image.
First, to count the unique values with blanks in an excel column, click on an empty cell, in this case cell C2, and enter the formula as
=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12,A2:A12&"")) and click Ctrl + Shift + Enter.
Empty Cell > Formula > Ctrl + Shift + Enter.
This is how you can count unique values with blanks in Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can count unique values with blanks in an Excel column to highlight a particular set of data.
186 Views
To Continue Learning Please Login