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
Unique values in any column are those that have only one occurrence, and that 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 a function, we will get the sum of the count of unique cells. This is an array formula where we are using multiple functions. To exit from the syntax, press Ctrl + Shift + Enter together, which 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, 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 or identify or take a single character count.
- Asterisk (*): To match any sequence of leading or trailing characters.
- Tilde (~): To find out an actual question mark or asterisk.
COUNTIF function is purely based on a logic that 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 by 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). Here, 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, 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 needs to be filled or entered, i.e. =COUNTIF (Range, criteria)
- Range: To enter the range argument, click inside cell C8 and you’ll see the cell selected, then Select the cells till C24. So that column range will get selected, i.e. C8:C24.
- Criteria: It is a condition that tells the countif function which cells to count, i.e. 35.
Click ok after entering both the argument.
=COUNTIF (C8:C24,35)
i.e. returns the count of unique scores, i.e. “35” from this dataset range (C8:C24). Click OK
23 Online Courses | 9 Hands-on Projects| 110+ Hours| Verifiable Certificate of Completion
4.9
View Course
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 the 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, 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 needs to be filled or entered, i.e. =COUNTIF (Range, criteria)
- Range: To enter the range argument, click inside cell H5 and you’ll 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”.
Here, we are using a text value or condition; we have to type it within double quotation marks. Another thing that you should know is the criteria. it is case insensitive, where you can also use “Tuesday” in lowercase format
Click ok after entering both the argument.
=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).
Suppose you want to count Saturdays and Sundays; you can still use this COUNTIF function. As both, the text value starts with “S”. we can use a simple syntax of COUNTIF.
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
- 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 Count 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 –
