Excel 2024: Formula for Unique or Distinct
August 05, 2024 - by Bill Jelen
![Excel 2024: Formula for Unique or Distinct Excel 2024: Formula for Unique or Distinct](/img/excel-tips/2024/08/excel-2024-formula-for-unique-or-distinct.jpg)
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.](/img/content/2024/08/LXFig-91.jpg)
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.](/img/content/2024/08/LXFig-114.jpg)
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.](/img/content/2024/08/LXFig-60.jpg)
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](/img/content/2024/08/LXFig-124.jpg)
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.](/img/content/2024/08/LXFig-125.jpg)
This article is an excerpt from MrExcel 2024 Igniting Excel
Title photo by Drazen Nesic on Unsplash