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