How to Get Unique Values Using a Formula in Excel

To get a list of unique values you can use the AutoFilter or you can try using a formula approach.

The COUNTIF function only has two arguments, Range and Criteria.  The range would be your data range.  In the below example it’s A2:A9.  The criteria would be the value you want to count in the range.  So if we wanted to count the number of times the number 8 appeared in the data range below, the formula would be =COUNTIF(A2:A:9, 8).  This would return 3 because the value 8 occurs three times in the range specified.  Instead of setting the criteria to 8 we could point it to a cell containing that value.  So the formula would then be =COUNTIF(A2:A:9, A3).  It’s worth noting that Excel 2007/2010 has a new function called COUNTIFS which allows multiple ranges and criteria.

To get the unique values in a range lets use the COUNTIF function wrapped in an IF statement.  This is called nesting.  In Excel 2003 an IF statement can have up to seven levels of nested functions.  For 2007/2010, Excel can have up to 64 levels.  An IF statement has three arguments; a logical test, a value to be returned if the logical test is true, and a value to be returned if a logical test is false.  The logical test can be anything that results in a True or False value.

=IF(Logical Test, True Value, False Value)

So if we take our COUNTIF function and wrap it in an IF statement it would look something like this:

=IF(COUNTIF(A2:A9,A3),"True Value","False Value")

Now to apply the unique filter, we need to modify the logical test.  Right now, it’s not doing much, it’s just counting, and because we’re not testing anything yet, it will always return the true value argument.  To modify it, we can test whether the value returned by the COUNTIF function equals one.  If it does, then we know the value is unique.

=IF(COUNTIF(A2:A9,A3)=1,"True Value","False Value")

This will return the false value, because the value being counted is 8 (cell A3) and it appears three times in the list.

If we leave the formula as is and drag down, we will not get the results we’re expecting.  This is because the range is relative and will change with each row it’s being dragged down.  We need to change one of the references to absolute to make it count properly.  For more on Absolute vs. Relative References have a look at this article.  We also need to modify the range being counted, so we can get unique values in the range up to the current cell, in this case A3.  The new formula would be this:

=IF(COUNTIF(A$2:A3,A3)=1, "True Value","False Value")

We don’t want the value being returned to say “True Value” or “False Value”, so let’s either return the value in the data range (column A), or leave it blank if it’s not a unique value.  Our new formula is now this:

=IF(COUNTIF(A$2:A3,A3)=1,A3,"")

Now if we drag this down column B like in the above screenshot, you’ll see that only unique values appearing the first time will be listed.

OK, what if you want to list the unique values, but instead of the first unique value in a list, you want to find the last unique value in a list.  You’d use the same approach as the formula above, but you would tweek it slightly.

The absolute reference would change to the last row, instead first row of the range.

=IF(COUNTIF(A3:A$9,A3)=1,A3,"")

Last Unique Value

Lastly, what if you want to only list values that appear only once in the data range.  Again, it would be the same approach, but slightly different.

This time you would set both references of the row to absolute.

=IF(COUNTIF(A$2:A$9,A2)=1,A2,"")

Have a look at this sample workbook and play around with the examples.  There are lots of ways you can modify these examples to come up with different results depending on your requirements.



If you've any question, problem, suggestion and feedback than please comment below.

Have a nice day!