EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Count Unique Values in Excel

Count Unique Values in Excel

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 19, 2023

Count Unique Values in Excel Using COUNTIF

Count of Unique Values in Excel (Table of Contents)
  • Count Unique Values in Excel Using COUNTIF
  • COUNTIF Formula in Excel
  • How to Count Unique Values in Excel Using COUNTIF?

Count Unique Values in Excel Using COUNTIF

This is an array formula where we are using multiple functions. Unique values in any column have only one occurrence, which can be counted with the help of the countif function along with the SumIf function or Sum and If function. With the help of the Countif function, first, we will get the cell with unique values, and then with the help of the Sum and If the function, we will get the sum of the count of unique cells. To exit from the syntax, press Ctrl + Shift + Enter together, and we will frame the complete formula into a curly bracket.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

COUNTIF Formula in Excel

Below is the COUNTIF Formula in Excel :

COUNTIF Formula in Excel

The COUNTIF Function has two arguments, i.e., range and criteria.

  • Range: (Compulsory or required argument) It indicates the range of cells on which the criteria will be applied.
  • Criteria: (Compulsory or required argument) It is a condition that will be applied to the values presented by the range of cells. It indicates what needs to be counted.
Note: It can be a text string, cell reference, or mathematical/logical expression.
  • In the Criteria argument, Non-numeric values must always be enclosed within double quotes.
  • Criteria argument is case-insensitive, where it can consider a lower, proper & upper case.
  • The Wildcard characters can also be used in the criteria argument of the COUNTIF function.

The three most widely used wildcard characters in Excel COUNTIF functions are:

  1.  Question mark (?): Used to track, identify, or take a single character count.
  2.  Asterisk (*): To match any sequence of leading or trailing characters.
  3. Tilde (~): To find an actual question mark or asterisk.

The COUNTIF function is purely based on the logic you apply in the criteria argument.

Various arithmetic operators can be used in the COUNTIF function:

< Less Than
> Greater Than
= Equal to
<= Less than or equal to
>= Greater than or equal to
<> Less than or greater than

The above-mentioned operators are used in the Criteria argument of the COUNTIF function. 

How to Count Unique Values in Excel Using COUNTIF?

Count Unique Values in Excel Using COUNTIF is straightforward and easy to use. Let us understand the count of unique values in Excel with some examples.

You can download this Count Unique Values Using COUNTIF Excel Template here – Count Unique Values Using COUNTIF Excel Template

Count Values for a Given Unique Number Value in Excel – Example #1

In the below-mentioned example. The Table contains the student’s name in column B (B8 to B24) & the score of each student (C8 to C24). There is a repetition of scores; here, I need to get the count of a unique score, i.e., “35” from this dataset range (C8:C24).

Let’s apply the COUNTIF function in cell “E9”. Select the cell “E9”, where the COUNTIF function needs to be applied.

Excel Data Example 1

Click the insert function button (fx) under the formula toolbar; a dialog box will appear, type the keyword “COUNTIF” in the search for a function box, and the COUNTIF function will appear in the select a Function box. Double-click on the COUNTIF function.

Count Unique Values Example 1-2

A dialog box appears where arguments for the COUNTIF function need to be filled or entered, i.e., =COUNTIF (Range, criteria)

  • Range: To enter the range argument, click inside cell C8 to see the cell selected, then Select the cells until C24. So that column range will get selected, i.e., C8:C24.
  • Criteria: The condition tells the countif function which cells to count, i.e., 35.

Click ok after entering both argument.

=COUNTIF (C8:C24,35)

i.e., returns the count of unique scores, i.e., “35” from this dataset range (C8:C24). Click OK

Count Unique Values Example 1-3

Here, the COUNTIF function has returned a count of unique scores, i.e., “35” within a defined range, i.e., 4. The passing score has appeared 4 times in a dataset range (C8:C24).

Count Unique Values Example 1-4

Count Values for a Given Unique Text Value in Excel – Example #2

In the below-mentioned example, the Table contains a date in column G (G5 to G20) & a day in column (H5 to H20). Here, there is a repetition of days; here, I need to get the count of the unique day, i.e., “Tuesday”, only from this dataset range (H5:H20).

Let’s apply the COUNTIF function in cell “J7”. Select the cell “J7”, where the COUNTIF function needs to be applied.

Excel Data Example 2

Click the insert function button (fx) under the formula toolbar; a dialog box will appear, type the keyword “COUNTIF” in the search for a function box, and the COUNTIF function will appear in the select a Function box. Double-click on the COUNTIF function.

Count Unique Values Example 2-2

A dialog box appears where arguments for the COUNTIF function need to be filled or entered, i.e., =COUNTIF (Range, criteria)

  • Range: To enter the range argument, click inside cell H5 to see the cell selected, then Select the cells till H20. So that column range will get selected, i.e., H5:H20.
  • Criteria: It is a condition that tells the countif function which cells to count or cell containing text value, i.e., “TUESDAY”.

Another thing that you should know is the criteria. Here, we use a text value or condition; we must type it within double quotation marks. It is case insensitive, where you can also use “Tuesday” in lowercase format.

Click ok after entering both arguments.

=COUNTIF (H5:H20,”TUESDAY”)

i.e., returns the count of a unique text value, i.e., “Tuesday” from this dataset range (H5:H20). Click OK.

Count Unique Values Example 2-3

Here, the COUNTIF function has returned a count of unique text values, i.e., “Tuesday,” within the defined range, i.e., 3. Tuesday has appeared 3 times in a dataset range (H5:H20).

Count Unique Values Example 2-4

We can use a simple syntax of COUNTIF. Suppose you want to count Saturdays and Sundays; you can still use this COUNTIF function. As both the text value starts with “S,”.

Here we can use a wild character, an asterisk (*), to match a leading character sequence, i.e., “s.”

Let’s check out how it works; here, I use formula =COUNTIF (H5:H20, “S*”)

Count Unique Values Example 2-5

Where it returns the count of Saturdays and Sundays, i.e., 4

Final Result

Things to Remember about Count of Unique Values in Excel

  • The COUNTIF function does not apply to a non-adjacent range of cells.
  • Suppose the text string in the criteria argument contains more than 255 characters or greater than 255 characters in length. Then #VALUE! Error occurs.

Recommended Articles

This has been a guide to Counting unique values in Excel using COUNTIF. Here we discuss the COUNTIF Formula in Excel and how to count unique values in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

  1. COUNTIF Excel Function
  2. Excel Count Function
  3. Count Characters in Excel
  4. Count Cells with Text in Excel
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Excel VBA Bundle500+ Hours of HD Videos | 15 Learning Paths | 120+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Count Unique Values Using COUNTIF Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Count Unique Values Using COUNTIF Excel Template

EDUCBA

डाउनलोड Count Unique Values Using COUNTIF Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more