In large excel datasets, you may come across duplicate and unique values. You may want to know how many unique values you are working with. Unique values are those values that appear only once in a list. These values have no duplicates. For example, in a list that has the names; Tom, Dick, Harry, and Tom. Your unique values will be Dick and Harry.
Here, we show you the different ways of counting unique values in an excel worksheet.
Using the Advanced filter to count unique values in Excel
One way of counting unique values is using the Advanced Filter function in Excel. From this function, you can extract your values from a column or row of data and paste the results into a new location. Afterward, you can use the ROW function in Excel for the number of items that your unique values will represent.
1. On your computer, open the worksheet that contains the unique values you want to count.
2. Select the range of cells that contain the data set. It is good to make sure that the selected range of cells has a column heading.
3. On the main menu, click on the Data tab.
4. Under the Sort & Filter group, click on the option Advanced to display a dialog box.
5. In the Advanced Filter dialog box, click the option 'Copy to another location.'
6. Next, go to the box 'Copy to' and enter a cell reference. In case you are not sure of the cell range, you can click on the collapse dialog to temporarily hide the Advanced Filter dialog box. Afterward, select a cell reference in your worksheet and click on the expand dialog icon.
7. Next, check the box 'Unique records only' and click OK. Afterward, you will notice that your unique values from the selected range will be copied to the new location. They will begin with the specified cell reference you entered in the step above.
8. Lastly, in the last blank cell below your range values, enter the ROWS function. Here, you will use the range of unique values you copied as the argument excluding the column header. For example, if you have a unique range of values from cell A2:A20, your function will be =ROWS(A2:A20) or you can use the formula =COUNTA(B2:B10)
Counting unique values in excel using formulas
You can also count the number of unique values in your worksheet using the sum-product and COUNTIF formulas. Here, you will use the generic formula;
=SUMPRODUCT (1/ COUNTIF (data, data)) whereby data represents the range that contains the values.
For example, let's say you have a range of values from cell B5:B14. Such a range will have 10 values for your criteria.
Your COUNTIF value range will be COUNTIF(B5:B14, B5:B14)
Your array results will be displayed as {3;3;3;2;2;3;3;3;2;2}
When you use the formula with one as the numerator, you will get a new array of {0.3333;0.3333;0.3333;0.5;0.5;0.3333;0.3333;0.3333;0.5;0.5}
Afterward, the SUMPRODUCT function will sum all the values within your array and return your final value.
Apart from the COUNTIF formula, we also use the SUM function concerning the IF and COUNTIF functions.