Count the Unique Values in a Range
By John Cellini
Published on August 11, 2007
Recently, a student asked how she could count each item in a long column that was repeated several times; she only wanted to count each item once. In other words, how does one count the unique values in a range?
This tutorial will demonstrate how to count the number of unique values in a range using a function. I am providing a couple of solutions. The first solution is simpler but longer than the second. The first solution is designed to give you a better understanding of how Excel functions handle duplicate items in a column.
You can also use Excel's Advanced Filter to find unique values. The instructions for this is in the sidebar on the right.
Before we begin, you need to download the following practice file. We will refer to this workbook throughout this tutorial:
A Simple But Long Solution
First we need to consider how some Excel Functions handle duplicate items in a column. In the Unique Values Practice spreadsheet, enter the following formula in cell F4:
=COUNTIF(A$4:A4,A4)
Copy this formula down the column through cell F52.
This is your basic COUNTIF function. Note the row for the first cell reference in the range is absolute (A$4). As you copied the formula down the column, the first cell in the range will remain A4 while the last cell in the range will change according to the location of the copied formula.
Once you have the COUNTIF function copied through A52, we can see that Excel has built an array of the number of occurrences for each item. Each individual element in our range is indexed. For example, the first instance of “Jerry Seinfeld” is given an index of “1” and the last instance is given an index of “8” because “Jerry Seinfeld” is listed 8 times.
Now to see how many unique items there are all we need to do is enter another simple COUNTIF function in cell H4:
=COUNTIF(F4:F52, "1")
We see that there are 5 unique items.
In the above example, we first created a column to index the agents. Let’s do it a different way this time. Enter the following formula in cell G4:
=IF(COUNTIF(A$4:A4, A4)=1, "X", "")
Here we have the same COUNTIF function nested in the first argument of an IF function. If a cell in our range contains “1,” “X” will be entered, otherwise the cell will remain blank. If the column is not very long, we can see at a glance how many exes are returned.
Now in cell H4, we can change the formula to the following:
=COUNTIF(G4:G52, "X")
page 1 | 2 | next page >>