Sometimes you need to know the number of unique values in a range of cells. For instance, suppose that an instructor was teaching the following classes:
104-120 104-101 104-119 104-120
In this case there are three unique values. There is no intuitive worksheet function that will return a count of unique values, which makes one think that a user-defined function would be the logical approach. However, you can use an array formula to very easily derive the desired information. Follow these steps:
=SUM(1/COUNTIF(MyRange,MyRange))
{=SUM(1/COUNTIF(MyRange,MyRange))}
That's it! The cell now contains the number of unique name values in the specified range. This approach is not case-sensitive, so if you have two values that differ only in their capitalization (ThisName vs. THISNAME), they are both counted as a single unique value. In addition, there can be no blank cells in the range. (Having a blank cell returns a #DIV/0 error from the formula.)
If your particular needs require that your list contain blanks (but you don't want them counted) and you want the evaluation to be case-sensitive, then you must turn to a macro. The following macro, CountUnique, will do the trick:
Function CountUnique(ByVal MyRange As Range) As Integer Dim Cell As Range Dim J As Integer Dim iNumCells As Integer Dim iUVals As Integer Dim sUCells() As String iNumCells = MyRange.Count ReDim sUCells(iNumCells) As String iUVals = 0 For Each Cell In MyRange If Cell.Text > "" Then For J = 1 To iUVals If sUCells(J) = Cell.Text Then Exit For End If Next J If J > iUVals Then iUVals = iUVals + 1 sUCells(iUVals) = Cell.Text End If End If Next Cell CountUnique = iUVals End Function
Simply put an equation similar to the following in a cell:
=CountUnique(MyRange)
The value returned is the number of unique values, not counting blanks, in the range. Understand, as well, that as your range (what you defined as MyRange, earlier) becomes larger, the macro takes longer to process. This is understandable; it has to work through all the cells in the range, and if there are a lot of cells it can take a lot of time.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9872) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Counting Unique Values.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
It's easy to use filtering to hide rows based on the value in a cell, but how do you hide rows based on the values in two ...
Discover MoreRemember your number line from your early years in school? Some numbers can be below zero (negative numbers) and others ...
Discover MoreIt is easy to use Excel functions to sum values based on criteria you establish, unless those criteria involve the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2015-08-24 12:40:34
Yvan Loranger
Why not use Remove Duplicates then =COUNTA(A:A) or whatever column your data resides in.
2015-08-22 20:52:36
Dick Downey
another possibility is a pivot table with the courses in the row and count of courses in data. it will sort in alpha order or can be sorted by the count either large to small or small to large
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments