How to Count Unique Values in Excel? 5 Easy Ways

Count Unique values in Excel is a function that may aid you to discover the values that happen solely as soon as in the massive checklist of Excel. We regularly want to report the Unique variety of clients bought, the variety of merchandise in our inventory, Checklist of areas our enterprise lined.  

On this article, we clarify how to depend distinctive values in an Excel column.

Earlier than shifting forward you must know the distinction between distinctive values and distinct values.

• Unique Values: These are the values that happen solely as soon as. Unique Values from the checklist {1,2,3,1,4,2,7,5,7} are {3,4,5}.
• Distinct Values: These are the Values which might be totally different. Distinct values from the checklist are {1,2,3,1,4,2,7,5,7} are {1,2,3,4,5,7}.

How to Count Unique Values in Excel?

Unique Values in Excel seem in an inventory of things that happen solely as soon as. The principle intention of counting distinctive and distinct values is to separate them from the duplicates of the checklist in Excel.

There are numerous methods to discover the depend of distinctive and duplicates in Excel a few of them are as follows:

Pattern Knowledge

We now have given pattern information with two fields Buyer and Merchandise bought.  Want to create a abstract report to present what number of clients are in our database.

 

Count Unique Values in a Column Utilizing SUM and COUNTIF Capabilities

Utilizing SUM and COUNTIF formulation collectively. That is the simplest and easiest method to discover Distinct values in Excel.

Word: We will additionally use  SUMPRODUCT as an alternative of SUM.

Syntax:

COUNTIF (vary, standards)

• Vary – Checklist of knowledge 
• Standards – Numerous texts. Apply on the enter vary

Return an integer worth that matches the factors.

Step 1: Sort “Number of Customers” in the cell. 

Step 2: Write the under formulation in any cell, to depend the variety of distinctive clients.  We use the Array formulation to compute distinctive depend, So be sure you have to Press “Ctrl+Shift+Enter” for curly braces as proven in Picture 1 under:

Method:

” =SUM(1/COUNTIF(B2:B22,B2:B22)) “

Your formulation in cell D7 needs to be lined with “{}“ Curly braces  as proven under:

 

Word: The results of the COUNTIF formulation is used as a divisor with 1 because the numerator. This modifies the results of the COUNTIF formulation, the worth that seems solely as soon as in the array turns into 1 and the numbers which have duplicate values will grow to be fractions corresponding to the a number of.

How to Count Unique Textual content Values in Excel

In case your Excel checklist accommodates each textual content and numerical values and also you need to depend solely distinctive textual content values then you may depend distinctive textual content values in Excel by utilizing ISTEXT Perform to the formulation.

Syntax:

” =SUM(IF(ISTEXT(A2:A10)*COUNTIF(A22:A10,A2:A10)=1,1,0)) “

ISTEXT capabilities return TRUE if the checked worth is textual content worth or else FALSE.  Right here in the formulation (*) asterisk works because the AND operator in the formulation, which suggests the IF perform returns 1 provided that a price is each textual content and distinctive, In any other case 0. And the SUM perform provides up all 1’s and you may get the depend of textual content values because the output.

Word: At all times keep in mind to press Ctrl + Shift + Enter for an array formulation.

 

As you may see the formulation returns the worth 2, as a result of we now have solely two textual content +distinctive values. 

How to Count Unique Numeric Values in Excel

In case your Excel checklist consists of each forms of values numeric in addition to textual content. However you need the Count of Numeric that's distinctive in the checklist. In that case, you should use the ISNUMBER perform in the formulation.

Syntax : 

” =SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0)) “

 

Count Case – Delicate Unique Values in Excel

The straightforward method to depend distinctive values when you've got Case-Delicate information could be making a helper column with the next array formulation to establish duplicates and distinctive objects.

Method:

” =IF(SUM((–Actual($A$2:$A$10,A2)))=1,”Unique”,”Duplictae”) “

after which use a COUNTIF perform to depend distinctive values:

” =COUNTIF(B2:B10,”Unique”) “

 

Within the above picture, you may see the  Count of distinctive values is 5.

How to use the UNIQUE Perform to Count Unique Values in Excel

In case you are utilizing MS Workplace 365, you should use the under formulation to compute the distinctive depend.

” =COUNTA(UNIQUE(B2:B30) “

Syntax:

COUNTA(Value1, Value2,…..)

• Value1 – A cell, excel vary, an array of textual content
• Return depend of non-empty cells.

UNIQUE(array, by_col, exactly_once)

• Array – checklist of knowledge values to return distinctive
• by_col – Boolean worth [ True – return unique columns/ False -return unique rows]
• Exactly_once – Boolean worth [return only distinct values ]

How to use the COUNTA Perform to Count Unique Values in Excel

COUNTIFS permits you to depend values primarily based on a number of standards. COUNTIFS Perform counts the distinct values from the vary.

Method used is: Sum( 1/COUNTIFS(B2:B22,B2:B22))

 

Word: Don’t Neglect to press the “CTRL+SHIFT+ENTER” to put curly brackets to use an Array formulation. As a result of the array formulation doesn’t work in older variations of Excel. 

How to Count Distinct Values Utilizing the Superior Filters in Excel

Superior filters are a function in Excel that enables you to specify advanced standards utilizing formulation.

To make use of superior filters in Excel, Person wants to go to the ” DATA” tab, choose the “Advanced Filter” choice and outline the vary standards vary and the output vary.

This could additionally aid you to discover Distinct values in Excel.

Subtotal is the perform that can be utilized to depend solely the seen objects in your checklist. As proven in the under picture, Right here 103 argument is used to inform the SUBTOTAL perform to Count all of the seen objects in the actual vary.

 

Steps to depend the Distinct values utilizing the Superior Filter

Step 1: Choose the column.

Choose the column in which you need to depend the distinct values from the desk.

Step 2: Choose the Advance filter.

Now go to the “Data” choice in the ribbon and choose “Advanced Filter” from it.

Step 3: Tick the Unique data solely.

Step 4: Press “OK”

Step 5: Now you may see the depend of distinct data solely.

How to Count Distinct Worth Utilizing Pivot Desk

Observe the steps to Count Distinct values utilizing the Pivot desk:

Step 1: Create a Pivot Desk of the Knowledge

You first want to create a pivot desk of Knowledge in which you want to depend the Distinct values.

Step 2: Add your merchandise like Buyer title into Rows by dragging

Step 3: Now as you may see the Row labels with the Distinct values in the column, Choose the vary of knowledge of which you need the Count of Distinct values.

Step 4: Preview Count of Distinct Values

How to Count Distinct Values Utilizing VBA

There isn't any in-built Perform in Excel to Count the distinct values from the vary. However with the assistance of VBA, we will make our perform depend the distinct Values in Excel.

Observe the steps to Count distinct values utilizing VBA:

Step 1: Open the VBA

Choose the Developer Tab in the ribbon then choose Visible Fundamental, Now click on on Insert and go to the module. 

Step 2: Now write the Under code on the VBA display

Step 3: This perform will aid you to create a brand new perform in Excel COUNT DISTINCT VALUES which can be utilized similar to different capabilities in Excel.

You should utilize the above VBA code to create a brand new perform to depend distinct values in Excel. 

Additionally Learn: Countif Perform in Excel

How to Count Unique Values in Excel – FAQs

Is there a formulation to depend distinctive values in Excel?

Sure, you should use a formulation to depend distinctive values in Excel. One widespread method to do that is by utilizing the mixture of the COUNTIF and SUM capabilities together with an array formulation. Right here’s how you are able to do it:

What are Unique Values in Excel?

Unique Values in Excel seem in an inventory of things that happen solely as soon as. The principle intention of counting distinctive an distinct values is to separate them from the duplicates of the checklist in Excel.

How we will Count distinct values in a number of columns concurrently in Excel?

We will depend distinct values in a number of columns with the assistance of the SUM and COUNTIFS capabilities collectively.

• Step 1: Choose any random cell the place you need the outcome to show.
• Step 2: Use the Method “=SUM(1/COUNTIFS(range1,range1, range 2,range2…)” the place ranges are the cells in which you need to depend the distinct values.
• Step 3: Press “CTRL+SHIFT+ENTER” due to an array formulation.

What are the distinct values in Excel?

Distinct values in Excel are the distinctive values that seem solely as soon as in a variety of cells. For instance, distinct values from the Vary {A, B, B, C, C, D}  are {A, B, C, D}.

Clarify the formulation “=SUM(1/COUNTIFS(B5:B14,B5:B14)”.

The COUNTIFS perform is used to see what number of occasions a price seems in the checklist. This provides you with a fractional worth that can add up to 1 for every distinct worth in the checklist after inverting this. 

And SUM perform then provides all of the fractions up and the entire is the variety of distinct values in the checklist.  

Is there any In-built perform to depend the distinct values in Excel with out utilizing any formulation?

There isn't any Inbuilt perform in Excel to depend Distinct values with out utilizing formulation. The choice method is to use the “ Remove Duplicates” function, This can aid you to take away duplicates and provide the Count of distinctive values.  

Enhance