Written by Allen Wyatt (last updated May 24, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
Vince has a column that has several hundred numeric values in it. He needs to sum the four largest unique values in the column. In other words, if the five largest cells contained 21, 37, 22, 13, and 21, then he would need the sum of 21, 37, 22, and 13 instead of 21, 37, 22, and 21 because 21 is not unique in the results.
At first, you might think that the answer is to just sum the four largest values, in this manner:
=SUM(LARGE(A:A,{1,2,3,4}))
This won't work, however, because there could be a tie between those four largest values, which means that you aren't summing unique values. The solution is to use the UNIQUE function within your formula, in this manner:
=SUM(LARGE(UNIQUE(A:A),{1,2,3,4}))
The UNIQUE function returns an array of the unique values in column A, and then LARGE returns the four largest values from that array. Finally, SUM returns the sum of those values.
This approach is great if you are using Excel 2021 or later. (The UNIQUE function was added in Excel 2021.) If you are using an older version, then your best bet is to use a macro. Here's a user-defined function that you can use for this purpose:
Function SumTop4Unique(rng As Range) As Double Dim c As Range Dim Uniques() As Double Dim ExistsFlag As Boolean Dim lCount As Long Dim J As Long Dim K As Long Dim temp As Double lCount = 0 ' Loop through range and store unique numeric values in array For Each c In rng If IsNumeric(c.Value) And Not IsEmpty(c.Value) Then ExistsFlag = False For J = 1 To lCount If Uniques(J) = c.Value Then ExistsFlag = True Exit For End If Next J If Not ExistsFlag Then lCount = lCount + 1 ReDim Preserve Uniques(lCount) Uniques(lCount) = c.Value End If End If Next c ' Sort the array in descending order For J = 1 To lCount - 1 For K = J + 1 To lCount If Uniques(K) > Uniques(J) Then temp = Uniques(J) Uniques(J) = Uniques(K) Uniques(K) = temp End If Next K Next J ' Sum the top 4 (or fewer) values K = 4 If lCount < K Then K = lCount For J = 1 To K SumTop4Unique = SumTop4Unique + Uniques(J) Next J End Function
In order to use the macro in your worksheet, you would use the following if you wanted to sum the four largest unique values in column A:
=SumTop4Unique(A:A)
The macro pulls all the unique values from column A into an array (Uniques), sorts the array, and then sums the top four values.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10935) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
Got a list of data from which you want to delete duplicates? There are a couple of techniques you can use to get rid of ...
Discover MoreIf you need to count the number of values that fall between a lower and upper value, then you can use a number of ...
Discover MoreIf you have a range of values that can be either positive or negative, you might wonder how to determine the largest ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-05-29 17:10:59
J. Woolley
As described in my earlier comment below, the UNIQUE and UniquePlus functions return unique rows or columns of a contiguous range or array.
My Excel Toolbox now includes the following function to return unique values ignoring rows and columns:
=UniqueItems(Items, [SkipEmpty], [ExactlyOnce], [CaseSensitive])
Items can be any range or array; it is treated as a basket of values without structure. If Items is a noncontiguous cell range, it must be parenthesized.
If SkipEmpty is TRUE, empty values (e.g., empty cells) will be ignored.
If ExactlyOnce is TRUE, values that occur more than once will be ignored.
If CaseSensitive is TRUE, text comparisons will be case-sensitive.
Optional parameters are FALSE by default.
This function returns an array with 1 row and N columns; use TRANSPOSE to return a column with N rows. There will be no repetition in the result.
Here's an abbreviated version:
Function UniqueItems(ByVal Items As Variant, _
Optional SkipEmpty As Boolean, Optional ExactlyOnce As Boolean)
Dim Uniq As Object, Once As Object, isRange As Boolean, item As Variant
Set Uniq = CreateObject("Scripting.Dictionary")
If ExactlyOnce Then Set Once = CreateObject("Scripting.Dictionary")
isRange = (TypeOf Items Is Range)
If isRange Then Set Items = Intersect(Items, Items.Worksheet.UsedRange)
For Each item In Items
If isRange Then item = item.Value
If Uniq.Exists(item) Then
If ExactlyOnce Then
If Once.Exists(item) Then Once.Remove item
End If
Else
Uniq.Add item, Null
If ExactlyOnce Then Once.Add item, Null
End If
Next item
If ExactlyOnce Then
If SkipEmpty And Once.Exists(Empty) Then Once.Remove Empty
UniqueItems = Once.Keys
Else
If SkipEmpty And Uniq.Exists(Empty) Then Uniq.Remove Empty
UniqueItems = Uniq.Keys
End If
End Function
2025-05-28 12:37:41
J. Woolley
Correction: My most recent comment below said UNIQUE "treats empty cells as zero values." Actually it treats them as empty values and returns them as empty values, but when a function in a cell formula returns an empty value it is displayed as zero (0). UniquePlus works like UNIQUE in this respect.
Note ISBLANK returns TRUE for an empty value but FALSE for a blank text value (""), a.k.a. null string; therefore, ISBLANK should be named ISEMPTY.
For more on this subject, see https://excelribbon.tips.net/T010105_Returning_Zero_When_a_Referenced_Cell_is_Blank.html
2025-05-27 12:30:09
J. Woolley
Re. my previous comment below, LARGE and SMALL ignore empty cells plus text and logical values, but not error values.
UNIQUE returns unique rows or columns of a contiguous range or array. It treats empty cells as zero values, includes text, logical, and error values, but ignores text case.
My Excel Toolbox includes the following function that works like UNIQUE but provides additional options:
=UniquePlus(RangeArray, [LeftRight], [ExactlyOnce], [HasHeader],
[CaseSensitive])
The first three parameters match Excel's UNIQUE function.
When HasHeader is TRUE, the first row (LeftRight=FALSE) or first column (LeftRight=TRUE) is returned even if it is not unique.
Case-sensitive comparisons are done when CaseSensitive is TRUE.
This function does not require Excel 2021 or later.
See https://sites.google.com/view/MyExcelToolbox
2025-05-25 15:11:15
Mike J
I think the title of this tip is a little ambiguous.
Given the data as shown, should 21 be counted once only, or should 21 be ignored because it is not unique?
The unique function can accommodate both interpretations, so if the source data contains 21,37,22,13,21,15,1,2,3 say:-
=SUM(LARGE(UNIQUE(A:A,,FALSE),{1,2,3,4})) returns 95, counting 21 once, but:-
=SUM(LARGE(UNIQUE(A:A,,TRUE),{1,2,3,4})) returns 87, ignoring 21 as it is not unique in the source data.
2025-05-25 11:53:16
J. Woolley
The UNIQUE function requires a contiguous range or array.
The Tip's UDF requires a range (contiguous or not) but will not accept an array. This is appropriate given Vince's requirement described in the first paragraph.
As implied by Ken Kast, here's a more efficient version that works with any range or array:
Function SumTop4Unique2(rng As Variant) As Double
Dim lastVal As Double, nextVal As Double, J As Integer, K As Integer
lastVal = WorksheetFunction.Small(rng, 1)
Do
J = J + 1
Do
K = K + 1
nextVal = WorksheetFunction.Large(rng, K)
Loop Until nextVal <> lastVal
lastVal = nextVal
SumTop4Unique2 = SumTop4Unique2 + lastVal
Loop Until J = 4
End Function
Consistent with the function's name, if there are less than 4 unique values the result is a #VALUE! error.
2025-05-24 16:55:26
Ken Kast
Since you're going to sort anyway, wouldn't it be easier to first sort the list, then have a loop that starts at the top and adds successively, skipping an entry if it's the same as the previous. You'd exit the loop when you've added four numbers.
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 © 2025 Sharon Parq Associates, Inc.
Comments