MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2024: Formula for Unique or Distinct

August 05, 2024 - by

Excel 2024: Formula for Unique or Distinct

The UNIQUE function will provide either a list of unique or distinct values.

=UNIQUE has three arguments: array, by_col and occurs_once. Only the array is required.
=UNIQUE has three arguments: array, by_col and occurs_once. Only the array is required.

If you ask me for the list of unique values from this list: Apple, Apple, Banana, Cherry, Cherry, I would tell you that the list of unique values is Apple, Banana, Cherry. If you ask 100 accountants, about 92 of them would agree with me. But there is a segment of the population who disagrees and says that the only unique thing in the list is Banana because it is the only item that appears once.

This unusual definition of "unique" comes from the SQL Server world, where database pros would say that "Apple, Banana, Cherry" is a list of distinct values and Banana is the only unique value.

The new UNIQUE function will return either list. If you simply ask for =UNIQUE(R5:R9), you get my definition of all values that occur one or more times. But, if you are a database pro or Casey Kasem, then you can put a True as the third argument.

You start with a list of Apple, Apple, Banana, Cherry, Cherry. Use =UNIQUE(R5:R9) to get Apple, Banana, Cherry. Use =UNIQUE(R5:R9,,TRUE) to get just Banana.
You start with a list of Apple, Apple, Banana, Cherry, Cherry. Use =UNIQUE(R5:R9) to get Apple, Banana, Cherry. Use =UNIQUE(R5:R9,,TRUE) to get just Banana.

Here is a list of all the Billboard Top 10 Hits from 1979 - 1993.

A database of Billboard Top 10 Music Hits from 1979-1993. ID is in column A, Artist in B, Track in C, and Genre in D.
A database of Billboard Top 10 Music Hits from 1979-1993. ID is in column A, Artist in B, Track in C, and Genre in D.

To get a list of genres, use =UNIQUE()

=UNIQUE(D4:D6132) returns a list of genres such as Rock, Easy, Vocal, Pop, R&B, Country, Folk

Bonus Tip: Use # "The Spiller" to Refer to All Array Results

In the previous screenshot, the UNIQUE function is in cell F5. You never really know how many results that formula will return. To refer to "the entire array returned by the formula in F5", you would write F5#. There is no official name for this notation, so I am using an idiom coined by Excel MVP Ingeborg Hawighorst: The Spiller.

Other Functions Can Now Accept Arrays as Arguments

Once you see the list of genres, you might want to know how frequently each genre appears. That would normally require a series of COUNTIF or COUNTIFS formulas. For example, =COUNTIF(D$4:D$6132,"Rock") would count how many songs were in the Rock genre. But rather than entering a bunch of COUNTIF functions, you could enter a single COUNTIF function and pass an array as the second argument. The formula below uses The Spiller syntax to ask Excel to repeat the COUNTIF for each answer in the UNIQUE function in F5.

The UNIQUE function is returning a list of Genres starting in F5. You want to use COUNTIF to count how many songs there were in each genre. But you have no idea how many rows there might be. A single formula of =SUMIF(D4:D6132,F5#) will return as many rows as were returned by the dynamic array in F5.
The UNIQUE function is returning a list of Genres starting in F5. You want to use COUNTIF to count how many songs there were in each genre. But you have no idea how many rows there might be. A single formula of =SUMIF(D4:D6132,F5#) will return as many rows as were returned by the dynamic array in F5.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Drazen Nesic on Unsplash