Excel: Getting the top five values from a row into a comma separated list

``````+-----+-----+-----+-----+-----+-----+-----+-----+
|     | A   | B   | C   | D   | E   | F   | G   |
+-----+-----+-----+-----+-----+-----+-----+-----+
| 1   | 5   | 6   | 5   | 9   | 4   | 8   | 3   |
+-----+-----+-----+-----+-----+-----+-----+-----+
``````

Considering the above sheet. I am looking for a way in Excel/Calc to get the top 5 numbers.

The only way I can think to do it so far is to use 5 different LARGE functions

``````=LARGE(A1:G1, 1)
=LARGE(A1:G1, 2)
=LARGE(A1:G1, 3)
=LARGE(A1:G1, 4)
=LARGE(A1:G1, 5)
``````

That would give me the five highest numbers. I was wondering if there was a way to do this with a single formula that would return:

``````9, 8, 6, 5, 5
``````

into a single cell?

Another question, instead of printing them out, I would also like to SUM the numbers in a different cell.

You can use TEXTJOIN() as an array formula.

``````=TEXTJOIN(",",TRUE,LARGE(A1:G1,ROW(\$1:\$5)))
``````

The `ROW(\$1:\$5)` when entered as an array returns the array `{1,2,3,4,5}` returning the top five largest numbers to the TEXTJOIN.

You force the Array by confirming the formula with Ctrl-Shift-Enter instead of Enter when Exiting Edit mode.

TEXTJOIN is available in Office 365 Excel.

To get the sum we can use the same LARGE inside a SUMPRODUCT:

``````=SUMPRODUCT(LARGE(A1:G1,ROW(\$1:\$5)))
``````

For older versions you are stuck with:

``````=LARGE(A1:G1, 1) & "," & LARGE(A1:G1, 2) & "," & LARGE(A1:G1, 3) & "," & LARGE(A1:G1, 4) & "," & LARGE(A1:G1, 5)
``````

Or helper cells.

• Thank you. I also figured out the answer to my second question. =SUMPRODUCT(LARGE(A1:H1,{1,2,3,4,5})) – Bobby S Apr 12 '18 at 19:10
• @BobbyS nevermind just finally read the last sentence. – Scott Craner Apr 12 '18 at 19:15