WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Count Unique Values in Excel

Count Unique Values in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Count Unique Values in Excel

To count only unique values in excel, we can adopt several options, and the top ones are shown below.

  1. Count unique values using Sum and Countif function.
  2. Count unique values using SUMPRODUCT and Countif function.

Now let us discuss each of the methods in detail along with an example –

#1 Count Unique Values using SUM and COUNTIF Function

Assume you are working as a sales manager, and you have sales data in front of you. Many customers purchased the product at multiple time frame in the month. You need total customers in the month.

You can download this COUNT Unique Values Excel Template here – COUNT Unique Values Excel Template

In this example, I will discuss the method of counting unique values using the SUM function in excel with the COUNTIF function.

  • Step 1: Copy the below data to your excel file.

Count Unique Values in Excel Step 1

  • Step 2: Apply the below formula in the cell E3 to get the total unique values.

Count Unique Values in Excel Step 2

Now let me break down the formula in detail.

The formula I have used here is:

Using SUM and COUNTIF formula 1

If you take a close at the formula, it is surrounded by curly brackets. It is not entered by me; rather, it is an indication that it is an array formula.

Once you finished with the formula before we hit enter, we need to use the key:

Ctrl + Shift + Enter this would enter those curly brackets automatically for you.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

This is a bit new formula if you are working with array formulas in excel for the first time. Let me break down the formula into three pieces.

Using SUM and COUNTIF formula 1-1

Firstly I will explain the COUNTIF formula. Select the countif formula part and press F9.

Using SUM and COUNTIF formula 1-2

Now press the key F9.

Using SUM and COUNTIF formula 1-3

Now we are dividing the above values by number 1.

Using SUM and COUNTIF formula 1-4

Now Sum function add all those numbers that are appeared in the above image and gives the total as 12. Therefore the total number of unique values in the list is 12.

How Does Calculation work?

  • If the values are there in the list two times, then it is ½, i.e., 0.5. If the value is there in the list three times, then it is 1/3, i.e., 0.3333.
  • In our list, the first name is Ruturaj that is appearing three times in the list, so our result showing 0.33333333 as the value.
  • Our second name Kamal appearing only one time, and the formula read like this 1/1 I .e.1 only.
  • This COUNTIF and SUM function can give us the total number of unique values in the list.

#2 Count Unique Values using SUMPRODUCT and COUNTIF Function

Take the same data from the previous example.

In this example, I will discuss the method of counting unique values using the SUMPRODUCT function with the COUNTIF function. This is not an array formula I am going to use in this example rather a normal formula I am using.

  • Step 1: Copy the below data to your excel file.

Count Unique Values in Excel step 1

  • Step 2: Apply the below formula in the cell E6 to get the total unique values.

Count Unique Values in Excel step 2

Now let me break down the formula in detail.

The formula I have used here is:

Using SUMPRODUCT and COUNTIF formula 1

In our previous example, I have used the Array formula, i.e., closing the formula with Ctrl + Shift + Enter.

If I break down the formula using the F9 key, it exactly works the same as the previous one.

Using SUMPRODUCT and COUNTIF formula 1-1

Now SUMPRODUCT function adds all those numbers that are appeared in the above image and gives the total as 12. Therefore the total number of unique values in the list is 12.

Handling Blanks in the Range

If the value list contains any of the empty cells, then the formula returns the result as an error in excel, i.e., #DIV/0!.

Handling Blanks 1

In the above image row, number 10 is the empty row. Since there is an empty row, the formula returned the result as an error, i.e., #DIV/0!.

We can handle these kinds of errors by inserting nothing ( “” ) value to it.

function 1-1

At the end of the countif formula, we need to nothing value by using an ampersand symbol then deduct the whole result by -1 because even the empty cell is treated as a unique value by the formula.

Note: If there are two empty rows, then we can use -2; if there are three empty rows, then we can use -3, and so on.

Things to Remember

  • We need to use Ctrl + Shift + Enter to close the formula in case of array formulas. Even when we are editing the formula, we cannot simply close the bracket; we need to Ctrl + Shift + Enter.
  • We can get the unique list just by removing duplicate values from the list.
  • An array formula cannot be applied to merged cells.

Recommended Articles

This has been a Guide to Count Unique Values in Excel. Here we use COUNTIF Function along with SUM and SUMPRODUCT to count unique values in Excel along with the excel template. You may also look at these useful functions in excel –

  • Calculate SUMPRODUCT with Multiple Criteria
  • COUNTIF with Multiple Criteria
  • ISNA Function in Excel
  • OFFSET Excel Example
8 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download COUNT Unique Values Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More