Sum the Top 5 Values - Excel Formula

In this video, we sum the top 5 values in a range using an Excel formula. This formula can easily be adjusted to sum the top 3, top 10 or whatever you need.

Think you need to improve your Excel formula skills?

In the video, the SUMPRODUCT and LARGE functions are used together to achieve our goal.

An array constant is used to return the top 5 values with the LARGE function. This element is what you need to change to return something different such as the top 3.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[theresemendez]

Worked first time I tried it! Thank you!

[shashwat]

Too good brother, very smooth. I was stuck at this since a while. Thanks!

[gopikisna]

got what i want, thank you, keep up your good help.👍🙏🏼

[SANTHIPRIYA-hn]

Thank you so much, I tried and it worked!

[dparel]

How would I do this if the #'s were in column's instead of rows? Like test scores listed for different names in A2:P2 then removing the lowest 3 scores?

[edwarddehaas]

This is very helpfull, thank you. Will it also be possible to insert criteria using the IF function (or similar) to select only numbers which belong to a certain category? Thanks in advance :)!

[mathsconcepts]

Very nice and informative
Thank you
👍🏼👍🏼👍🏼

[angjasonjason]

Sir, what if I want the top 40 values, do I have to manually type 1 to 40?

[davidclark]

Hi, I can’t get this to work on my numbers programme, I’m getting the red🔺 which says ‘formula contains a number outside the valid range’

I’ve converted the formula to text which reads as SUMPRODUCT(large(B3:U3, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}))

Any help much appreciated

[ssaagleninneswebsiteeditor]

Nice - my problem is for a monthly competition I am score-keeper for, I need to sum a competitors top 4 scores for the year to rank them for medals at year's end, but there is a risk with this approach, a competitor could get 5 or more 'equal top scores' and come first despite those scores being low ones because it seems to add all five up?
I have a way around this, but it involves lots of hidden columns! Is there a single cell formula that will sum as you show, but only to the number set?

[ashleymillin]

I'm trying to do this from values in a row but the sum of not from all columns in the row. It says I've returned too many arguments. I typed the individual cell in the row that I want to return a value for.

[ExcelSquad]

Sir you have to make a video about interview questions in excel.

[daveraskob]

What if the top five have the same value. I.E 10, 8, 8, 7, 5 being the top 5 of ten. Is there a way to work
around this?

[bernardblumberg]

Is it the same for average the top 5? Does average replace sumproduct..?

[sitedddd]

Hello comp.gaga, I have one unsolved problems.
I have random cut length of pipes 50.qty. example. 100 120 100 200 234 566 569 6007 320 780 777 777 100 900 400 420 400 .. and so on . Now I have one target value say 400. So I want select best closet value nfrom this list.
First criteria is How many pipe. If I select 1, then 400, 400 shall be shown. If I set 2 pipe, then 200+234 & 320+100 & 400+100 ...
If 3 pipe set in my criteria, then best closet pipe shall be select from list sum of this 3 pipe length equal or greater than my target value.
Possible? I think it very Hard.

[AlexGarcia-oiup]

october - Sancho - 300
october - sancho - 400
october - Sancho - 50
october -sancho - 60
november - sancho - 20
november sancho - 40
november - sancho - 50
november sancho - 40

Image those were the three columns ( date, name and amount ), how would i be able to calculate Sancho's top three scores in October and the same for November. i tried doing a sumifs formula but that doesnt work because it adds all the scores, i am only interested in the top three

[CharlieReid]

Sorry if this is an easy question. I want the top x but not from a row i.e A3:A9, i want the top x from cells A3, A7, A12 but don't know which how to or perhaps which symbol to use, tried comma etc. to no avail.
Can you help please.

[shalurajrajora]

Sir how you enter curly bracket....pls reply

[clancygyikay]

What if there are zeros among the top 5 values??

[martinhellawell]

I don't want a "range" I want the added values of 6 highest value cells out of 7?