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 value which has only one occurrence and that can be counted with the help of countif function is used along with SumIf function or Sum and If function. With the help of Countif function first, we will get the cell with unique values and then with the help of Sum and If 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 which will be applied to the values presented by the range of cells. It indicates what needs to be counted.
Note: It can be either a text string, cell reference or mathematical/logical expression.
- In Criteria argument, Non-numeric values must be always 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 criteria argument of COUNTIF function.
The three most widely used wildcard characters in Excel COUNTIF functions are:
- Question mark (?): Used to track or identify or take a count of a single character.
- 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 logic what you apply in criteria argument.
Various arithmetic operators can be used in COUNTIF function:
| < | Less Than |
| > | Greater Than |
| = | Equal to |
| <= | Less than or equal to |
| >= | Greater than or equal to |
| <> | Less than or greater than |
These above mentioned operators are used in Criteria argument of the COUNTIF function.
How to Count Unique Values in Excel Using COUNTIF?
Count Unique Values in Excel Using COUNTIF is very simple and easy to use. Let us understand the count of unique values in excel by some example.
4.9 (3,666 ratings)
View Course
Count Values for a Given Unique Number Value in Excel – Example #1
In the below-mentioned example. The Table contains Name of the student in column B (B8 to B24) & score of each student (C8 to C24). Here, in the dataset there is a repetition of scores, Here I need to get the count of unique score i.e. “35” from this dataset range (C8:C24).
Let’s apply COUNTIF function in cell “E9”. Select the cell “E9” where COUNTIF function needs to be applied.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “COUNTIF” in the search for a function box, COUNTIF function will appear in select a Function box. Double click on COUNTIF function.
A dialog box appears where arguments for COUNTIF function needs to be filled or entered i.e. =COUNTIF (Range, criteria)
- Range: To enter 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 score i.e. “35” from this dataset range (C8:C24). Click OK
Here, COUNTIF function has returned a count of unique score 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, Table contains a date in the column G (G5 to G20) & day in the column (H5 to H20). Here, In the dataset there is a repetition of days, Here I need to get the count of unique day i.e. “Tuesday” only from this dataset range (H5:H20).
Let’s apply COUNTIF function in cell “J7”. Select the cell “J7” where COUNTIF function needs to be applied.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “COUNTIF” in the search for a function box, COUNTIF function will appear in select a Function box. Double click on COUNTIF function.
A dialog box appears where arguments for COUNTIF function needs to be filled or entered i.e. =COUNTIF (Range, criteria)
- Range: To enter 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 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 unique text value i.e. “Tuesday” from this dataset range (H5:H20). Click OK.
Here, COUNTIF function has returned a count of unique text value i.e. “Tuesday” within 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, asterisk (*) to match a sequence of leading character i.e. “s”
Lets checkout, 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 is not applicable to a non-adjacent range of cells.
- If the text string in criteria argument contains more than 255 characters or greater than 255 characters in length. than #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 template. You can also go through our other suggested articles –
