Unique values by count

To extract a list of unique values from a set of data, filtered by count or occurrence, you can use UNIQUE with FILTER, and apply criteria with the COUNTIF function. In the example shown, the formula in D5 is: =UNIQUE(FILTER(data,COUNTIF(data,data)>1)) which outputs the 3 unique values that appear more than once in the named range data (B5:B16). Note: In this example, we are extracting a unique list of values that appear more than once. In other words, we are creating a list of duplicates :) The language is somewhat confusing.

April 14, 2025 · 1 min · 85 words · Bethany Mack

Table of Contents

Explanation

This example uses the UNIQUE function together with the FILTER function.

you might see amore basic example here.

This array is returned to FILTER as theincludeargument, used to filter the data.

Excel formula: Unique values

FILTER returns another array as a result:

This array is returned directly to the UNIQUE function as thearrayargument.

Notice of the 12 original values, only 8 survive.

The output from FILTER is dynamic.

Excel formula: Distinct values

If source data or criteria change, FILTER will return a new set of results.

COUNTIF can be used to count…

Excel formula: Unique values ignore blanks

Excel formula: Extract unique items from a list

Excel formula: Unique values with criteria

Excel formula: FILTER to show duplicate values

Excel UNIQUE function

Excel FILTER function

Excel COUNTIF function