Updated August 19, 2023
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.
COUNTIF Formula in Excel
Below is the 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.
- 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:
- Question mark (?): Used to track, identify, or take a single character count.
- Asterisk (*): To match any sequence of leading or trailing characters.
- 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.
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.
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.
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
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 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.
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.
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.
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).
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*”)
Where it returns the count of Saturdays and Sundays, i.e., 4
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 –
