If you are a regular Excel user, and you have stored huge data on spreadsheets, then there might be some duplicate values available. Counting unique values among the duplicate values on the spreadsheet will be a confusing task for anyone. So, we come up with a simple formula to get those unique values in the specified range on your spreadsheet. In this article, we will explain the formulas for counting the unique values in a range with COUNTIF Function in Excel. Get an official version of MS Excel from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Jump To
General Formula
Here we come up with a simple formula to count unique values in the specified range in your Excel spreadsheet with the help of the COUNTIF function.
=SUMPRODUCT(1/COUNTIF(data,data))
Syntax Explanations
- SUMPRODUCT – This function will multiply the corresponding array or range and returns the sum of the products. Read more on the SUMPRODUCT Function.
- COUNTIF – In Excel, this function will help to count the cells that match a single condition or criteria.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
- Data– It represents the input range from your worksheet.
- Division (/) – It is used for dividing values or numbers.
How to Apply Formula to Count Unique Values in a Range?
In order to count the unique values in the range, we can use an Excel formula. This formula can make use of the COUNTIF function for finding the total count of the unique values in the specified range in your worksheet. For instance, we will need to find unique values in from the list.
The following image will show you a list of fruits and count how many unique values are there.
After that, you can use the below-mentioned formula in any cell where you want to get the output result.
=SUMPRODUCT(1/COUNTIF(B3:B12,B3:B12))
Once you have entered the formula, click on the Enter button to execute it and get the result.
If the above-given scenario is not enough to understand this formula and want to learn this with a simple example, kindly move to the following section.
Alternate Example
Here we come up with a simple example to know how to apply this Excel formula in the spreadsheet.
- In the below example image, you can see the list of student names in the range. And we want to get the count of unique values from the list by ignoring duplicate values.
- Then, enter the given formula in a cell where you want to get the result.
=SUMPRODUCT(1/COUNTIF(B3:B12,B3:B12))
- Finally, you will need to click the Enter button to get the result, as shown in the below screenshot.
- From this example, you can easily understand how to count the unique values in Excel using a formula.
Wind-Up
This page describes the formulas used to count the unique values in a range using COUNTIF Function in Excel. Hope that this article is useful to you. Kindly, leave your valuable feedback in the below comment box. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel and Excel Formulas!
Video Tutorial
The following video will let you know how to apply a formula to count unique values in the range using the COUNTIF function.
Read Also:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
Hi there, I’m Sridhar – an Excel enthusiast with over 10 years of experience working with software. I’m passionate about using Excel to solve complex problems and streamline business processes. Over the years, I have helped businesses of all sizes to improve their operations and save time and money.
Aside from working with Excel, I also enjoy writing and sharing my knowledge with others. You’ll often find me contributing to the AAW Excel blog, where I provide tips, tricks, and tutorials that are easy to understand for readers of all skill levels.
