QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Count Unique Values in Excel 2016?

By Neha T Leave a Comment

It becomes messy to sort out the unique values in a large range of cells if it contains duplicate values. Let’s say if you want to identify or count unique values in excel, then you can build up your own formula for it.

There are various functions available in Excel using a combination of which you can implement the formula to count the unique values in Excel. In this section we will discuss some handy methods to count the unique values in excel:

  1. Count Unique Values using Formula
  2. Handle Blanks in the Selected Range
  3. Count Unique Values that are Text
  4. Count Unique Values that are Number
  5. Using Advanced Filter

Steps to Count Unique Values in Excel 2016:

Count Unique Values Using Formula

As above we have discussed that you can build your own formula to count the unique values in a range of cells. Here, we will see the construction of two formulas to count unique values.

1st formula

=SUM(IF(FREQUENCY(B2:B18,B2:B18)>0,1))

Now, let’s understand this formula

FREQUENCY: This function counts how many times the value has occurred in the selected range. It returns a vertical array which shows the frequency of occurrence of each number in the range.

Note: This function only considers non zero numeric value i.e. it avoids the text values and zeros.

Example: Let us check the frequency of each number in the range B2:B18. For this, first select an output-range (equal to the size of the reference range) and at the top cell of output-range type the formula:

=FREQUENCY(B2:B18, B2:B18)          (press Ctrl+Shift+Enter, as it an array formula)

See in the image below, at first occurrence of the value it shows, its total occurrence in the reference range, at rest occurrences, it shows 0.

Frequency function

Like at first occurrence of 100, it shows digit 6, it means 100 occurs 6 times in the reference range, at the rest occurrence of 100 it shows 0.

Now coming back to our main formula:

=SUM(IF(FREQUENCY(B2:B18,B2:B18)>0,1))

As we have seen in the figure above FREQUENCY function returns an array, calculating the occurrences of each value in the reference range. The array returned by FREQUENCY function is:

{6, 3, 0, 2, 0, 1, 1, 1, 0, 0, 0, 2, 0, 1, 0, 0, 0}

Now, IF function in the main formula checks each value of the array return by FREQUENCY function and for every non zero value it returns 1

IF({6, 3, 0, 2, 0, 1, 1, 1, 0, 0, 0, 2, 0, 1, 0, 0, 0}>0,1)

Result of IF: (1, 1, 1, 1, 1, 1, 1, 1)

The sum function adds all the 1s and generates the final result i.e. 8

=SUM(1, 1, 1, 1, 1, 1, 1, 1)

See in the image below the result of the formula:

=SUM(IF(FREQUENCY(B2:B18,B2:B18)>0,1))      (press Ctrl+Shift+Enter, as it an array formula)

Count unique value formula 1

2st formula

=SUMPRODUCT(1/COUNTIF(B2:B18,B2:B18))

Let’s see how this formula is going to work for us. Initiating with COUNTIF, unlike FREQUENCY, COUNTIF works for both text and numeric data as well.

Note: The COUNTIF function shows the total number of occurrences for the value each time it appears in the reference range.

In the picture below you can see how COUNTIF function works on text data:

=COUNTIF(A2:A18,A2:A18)           (press Ctrl+Shift+Enter, as it a array formula)

Countif for text data

Observe the figure above, Charles is repeated four times in the reference range and at each occurrence of Charles, COUNTIF displays digit 4.

The picture below shows how COUNTIF works for numeric data. 100 appears six times, so, for each occurrence of 100 it shows digit 6.

=COUNTIF(B2:B18,B2:B18)     (press Ctrl+Shift+Enter, as it a array formula)

Countif for numeric data

Coming back to our main formula:

=SUM(1/COUNTIF(B2:B18,B2:B18))

The COUNTIF in above formula returns the array {6, 3, 6, 2, 3, 1, 1, 1, 6, 2, 6, 2, 6, 1, 6, 3, 2} and the entire array values are divided by 1 now, the SUM formula will have:

=SUM(0.1666, 0.3333, 0.1666, 0.5, 0.3333, 1, 1, 1, 0.1666, 0.5, 0.1666, 0.5, 0.1666, 1, 0.1666, 0.3333, 0.5)

When this SUM executes the result it generates is 8

The picture below shows the result for the formula:

=SUM(1/COUNTIF(B2:B18,B2:B18))        (press Ctrl+Shift+Enter, as it a array formula)

Count unique value formula 2

For text data the formula below shows the result:

=SUM(1/COUNTIF(A2:A18,A2:A18))      (press Ctrl+Shift+Enter, as it a array formula)

Count unique value formula 2.1

Handle Blanks in the Selected Range

Guess what will happen to the above formulas if the selected range has a blank cell/s. Then it would return the error #DIV/0!. You can observe it in the image below.

handling blank cells

 

Well, a little change in the formula used above would help if we modify it in a way:

=SUM(1/COUNTIF(A2:A18,A2:A18&””))   (press Ctrl+Shift+Enter, as it a array formula)

handling blank cells 1

You can observe the result in the picture above, it is counting blank cell as a unique entry and is counting 1 for it.

To avoid the counting of the blank cell we will modify the formula in a way:

=SUM(1/COUNTIF(A2:A18,A2:A18&””))-COUNTIF(A2:A18,”<>”&”*”)           (press Ctrl+Shift+Enter, as it a array formula)

The formula COUNTIF(A2:A18,”<>”&”*”) returns 1  as it checks for the cell that are

handling blank cells 2

Now you can see it showing the correct result now.

Count Unique Values that are Text

In this section, we will see what we can do if have the reference range that has both numeric and text data and we want to count unique values only in the context of text.

The formula for this is:

=SUM(ISTEXT(A2:A10)/COUNTIF(A2:A10, A2:A10&””))           (press Ctrl+Shift+Enter, as it a array formula)

ISTEXT

You can see in the picture above that it counting only the unique text value by avoiding the blank cells and cells having the numeric value.

Count Unique Values that are Number

For counting the unique value in the context of number in a range of cell that has text data, numeric data and blank cells we constructed the following formula:

=SUM(ISNUMBER(A2:A10)/COUNTIF(A2:A10, A2:A10&””))

In the image below you can see the result obtained by implementing the formula above:

ISNUMBER

Using Advanced Filter

Step 1: This is an inbuilt function in Excel, for this, you have to go to the Data tab and in Sort & Filter group click on Advanced.

Advanced filter

Step 2: An Advanced Filter dialog box will pop out. Check the radio button “Copy to another location”.

Advanced filter dialog box

In the list, range box selects the range from where you want to derive the unique record.

In Copy to box select the range where you want to copy the unique record.

Check Unique record only box.

Advanced Filter for Text data:

Advanced filter Unique records

Advanced Filter for numeric data:

Advanced filter Unique records number

That was all about how you can count unique values in Excel. We have seen various method and formulas. It, not a limit to it you can explore it more ab constructs your own formula for it.

You Might Also Like:

Calculate Average in Excel feature imageHow to Calculate Average in Excel? Subtract feature image 1How to Subtract in Excel? compare two columns in Excel Feature imageHow to Compare Two Columns to Find Matches & Differences in Excel 2016? Sumif feature imageHow to SUMIF in Excel? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016? feature imageHow to COUNTIF in MS Excel 2016? enable macro featured imageHow to Enable Macros in Excel 2016? Calculate percentage Featured ImageHow to Calculate Percentage in MS Excel 2016?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to Convert Text to Number in MS Excel 2016?
  • How to Create a New Notebook in OneNote 2016?
  • How to Disable History in Firefox?
  • How to get Offline Maps from the Maps App in Windows 10
  • How to Host a Meeting in Zoom?

Copyright © 2023 · QueHow · Contact Us