The syntax for this combined formula is SUM IF 1COUNTIF data data110. UNIQUED2I2TRUE The second argument of TRUE is used to tell the function that the data is in a horizontal format.

INDEX B3B15 MATCH 0COUNTIF D2D2 B3B15 0 returns Dave.

. In the example shown the formula in D5 is. Use SUMPRODUCT COUNTIF to Get a Number of Unique Values from a List In the last method you have used the SUM and COUNTIF method. If you want to sort the list of names you can add the SORT function.

Unique function doesnt appear on my EXCEL. Not every Excel users get to use this wonderful function. INDEXlist row In other words give INDEX the list and a row number and INDEX will retrieve a value to add to the unique list.

The only downside is that UNIQUE is currently available in certain version of Excel. Example 4 Using UNIQUE across columns. Or click Sort Filter Filter on the Home tab in the Editing group.

The function UNIQUE returns the unique values contained in a column. Where emails is the named range C5C9. In Excel it has always been possible to extract without duplicates from a list with the menu Data Remove Duplicates in the ribbon.

Please see below how the formula figured on my Excel. UNIQUE function is purposefully made to get a distinct list of values. Here the COUNTIF formula counts the number of times each value in the range appears.

SUM 1COUNTIF range range Remember its an array formula and therefore you should press the Ctrl Shift. IFCOUNTIFA2A8 A21 Duplicate The formula will return Duplicates for duplicate records and a blank cell for unique records. To extract a list of unique values from a set of data ignoring blank cells you can use the UNIQUE function together with the FILTER function.

In the example shown the data validation applied to C5C9 is. In the example above cell K3 contains the following formula. COUNTIF emails C5 2.

Method 4 By Formula. INDEX arrayrow_num column_num The INDEX function returns the reference of cell meeting row and column number in a given range. It is only available only for Excel Microsoft 365 or Excel Online.

UNIQUEFILTER B5B16 B5B16 which outputs the 5 unique values seen in D5D9. Use TRANSPOSE to convert horizontal. The core of this formula is a basic lookup with INDEX.

By default Excel assumes UNIQUE should be applied on a vertical list. To allow only unique values in a given range you can use data validation with a custom formula based on the COUNTIF function. For this we enter the following formula in D2.

Below is an Excel unique values formula in its simplest form. The goal is to extract a list of unique names from the range B2B10. To return unique values from in the range A1A10 you can use a formula like this.

In case you want an Excel formula to find duplicates only replace Unique with an empty string like this. The hard work is figuring out the ROW number to give INDEX so that we get unique values only. If you format the range of names as an Excel table then the formula will automatically update when you add or remove names.

Please could you help me to resolve this issue I dont have the unique function on my formulas tab and when I receive a file with this formulas I cannot read it. Count unique values in Excel You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. This formula is quite powerful and it can help you to get the count in a single cell.

UNIQUEFILTERdata range1 b range2 5. UNIQUE A1A10 To return unique values from the horizontal range A1E1 set the by_col argument to TRUE or 1. Click the filtering arrow in the header of the column containing your formula and select the values you want to view.

In this step we extract the desired distinct value. However it can also work on a horizontal list. UNIQUE A1E11 extract unique from horizontal array.

The INDEX function helps to achieve it. Apply one of the above formulas to identify unique distinct values or rows. Basic UNIQUE formula in Excel.

Select your data and click the Filter button on the Data tab. Count distinct values in Excel unique and 1 st duplicate occurrences To get a count of distinct values in a list use the following formula.