Trending October 2023 # Count Unique Values In Excel (Examples) # Suggested November 2023 # Top 10 Popular

You are reading the article Count Unique Values In Excel (Examples) updated in October 2023 on the website Saigonspaclinic.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 Count Unique Values In Excel (Examples)

Count of Unique Values in Excel (Table of Contents)

Start Your Free Excel Course

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

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.

Note: It can be a text string,

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:

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.

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.

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

Criteria: The condition tells the countif function which cells to count, i.e., 35.

=COUNTIF (C8:C24,35)

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.

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

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.

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.