*Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011.*

This blog post will teach you how to count unique values among duplicates. It will explore various functions you can use to count unique values, such as FREQUENCY, MATCH, LEN, SUM, and IF.

Imagine that you are searching to establish how many unique values are present within a range that includes duplicate values. For example, if a column has:

- The values 7, 8, 9, and 8, the result is three unique values — 7, 8 and 9.
- The values “Abraham”, “Glenn”, “Glenn”, “Glenn”, the result is two unique values — “Abraham” and “Glenn”.

There are numerous methods to count unique values within duplicates.

## Count the number of unique values by using a filter

You can employ the **Advanced Filter** dialogue box to capture the unique values from a data column and paste them to a new repository. From there, you can select the **ROWS** function to calculate the number of items among the new range.

- Click the range of cells, or check that the active cell is in a table. Ensure the range of cells contains a column heading.
- On the
**Data**tab, in the**Sort & Filter**group, press**Advanced**. The**Advanced Filter**dialogue box emerges. - Select
**Copy to another location**. - In the
**Copy to**box, type a cell reference. Alternatively, press**Collapse Dialogue**to momentarily cover the dialogue box, pick a cell on the worksheet, and then click**Expand Dialogue**. - Choose the
**Unique records only**checkbox, and press**OK**. The unique values from the chosen range are relayed to the new location starting with the cell you confirmed in the**Copy to**box. - In the blank cell underneath the final cell in the range, type the
**ROWS**function. Reference the range of unique values that you recently copied as the argument, with the exception of the column heading. For example, if the range of unique values is B2:B45, you type**=ROWS(B2:B45)**.

## Count the number of unique values by using functions

## Examples

**Notes:**

- The formulas in this example must be typed as array formulas. If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press
**ENTER**to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing**CTRL+SHIFT+ENTER**to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas. - To see a function assessed step-by-step, pick the cell with the formula. Then, on the
**Formulas**tab, in the**Formula Auditing**group, select**Evaluate Formula**.

## Function details

- The
**FREQUENCY**function calculates how regularly values happen among a range of values. It subsequently returns a vertical array of numbers. For example, use**FREQUENCY**to count the number of test scores that fall among score ranges. Since this function yields an array, it must be inputted as an array formula. - The
**MATCH**function scans for a defined item in a cell range. Following this, it then captures the item’s relative position within the range. For example, if the range A1:A3 includes the values 5, 25, and 38, the formula**=MATCH(25,A1:A3,0)**returns the number 2. This is because 25 is the second item in the range. - The
**LEN**function gathers the number of characters in a text string. - The
**SUM**function accumulates each number that you elaborate as arguments. Each argument can comprise a range, a cell reference, an array, a constant, a formula, or the result from a different function. For example,**SUM(A1:A5)**calculates every number within the cell range of A1 to A5. - The
**IF**function gets one value if a condition you confirm evaluates to TRUE. Conversely, it will retrieve another value if that condition evaluates to FALSE.

## Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

## See Also

Filter for unique values or remove duplicate values

## Other Links

Back to Software Bytes – Official Website