How to count unique values within duplicates?

heart shaped gummy candy assorted in rows with one candy aside against pink background

  • 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.

  1. Click the range of cells, or check that the active cell is in a table. Ensure the range of cells contains a column heading.
  2. On the Data tab, in the Sort & Filter group, press Advanced. The Advanced Filter dialogue box emerges.
  3. Select Copy to another location.
  4. In the Copy to box, type a cell reference. Alternatively, press Collapse DialogueButton image to momentarily cover the dialogue box, pick a cell on the worksheet, and then click Expand Dialogue Button image .
  5. 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.
  6. 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 of nested functions to count the number of unique values among duplicates


  • 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

Back to Excel Tutorial

Leave a Reply

%d bloggers like this: