Summing the Four Largest Unique Values

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


6

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:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Watermarks in Excel

Excel is great at printing numbers on a piece of paper, but terrible at printing watermarks. This is apparently by ...

Discover More

Saving All Open Workbooks

Wouldn't it be nice to have a single command that would save each of you open workbooks, all at once? It's easy to do ...

Discover More

Removing Text Boxes but Saving the Text

Text boxes can be handy when it comes to noting information in a document or dealing with some tricky layout issues. If ...

Discover More

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!

More ExcelTips (ribbon)

Deleting Duplicate Text Values

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 More

Counting Values within 10% of a Target

If you need to count the number of values that fall between a lower and upper value, then you can use a number of ...

Discover More

Counting Consecutive Negative Numbers

If you have a range of values that can be either positive or negative, you might wonder how to determine the largest ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven less than 8?

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.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.