Sum and mark largest 5 numbers from selected range within column

Hi,

I'm trying to find a way to sum the 5 largest numbers in a column. I was using the LARGE function, however, it appears that the LARGE function only uses named ranges. Data will be coming into the worksheet daily and the ranges would change daily, so I need something to be more flexible with ranges.

In the attached spreadsheet the columns highlighted in yellow I'm looking to find an answer to, given the above whereby I need the ranges to be changed within the formula somehow...unless there's another solution!

Please help, I'm desperate!

Thanks,
SS.

try this in K2:

=SUMPRODUCT(LARGE(D$2:D$40, ROW($A$1:$A$5)))

(and pull to L2)

I've used =SUM(LARGE('range here',{1,2,3,4,5})). That works if the range is known, however, the range will change daily. I used ="$A$"&MATCH(A10,A:A,0)&":$A$"&MATCH(A10,A:A,0)+COUNTIF(A:A,A10)-1 to give me the range that I require, but I don't know how to use that within the LARGE function.

Can anyone please advise if this is even possible?

You could use Named Dynamic ranges

Sorry, you would have to replace A10 with A2 to get the correct range for the example spreadsheet, I copied it from another workbook. My apologies.

The formula you've given above produces #NUM error.

I need to name the ranges according to the PF column being the same, ie, all for GART1 and all for GART2. Each day, there will be varying amounts for GART1 and GART2. I've just had a read of your Dynamic Named ranges and it looks as though it may be what I'm looking for.

Would you mind further explaining how that might work in my scenario?

Thanks,
SS.

It is determined by that which is in column A in the example workbook I previously attached. So each range will start where there is a new value in column A. In my example the ranges I'd be looking to produce would be GART1 RNG which should be $A$2:$A$26 and a GART2 RNG which should be $A$27:$A$40. Come the next day, if there were 20 more entries for GART1 (column A is sorted from the original output) I'd need the dynamic range for GART1 RNG to be $A$2:$A$46 and GART2 RNG would be $A$47:$A$60.

Brilliant! That's part one of the problem solved!

Part 2 is the next 2 columns in the spreadsheet M & N where I need to somehow mark which of the GART1 and GART2 LMV's/SMV's are the top 5 per PF as I need to apply a separate calc specifically to those. A simple Yes or X or any sort of indication is fine.

I used the last one as it worked the best, however, if you require me to use any of the others I'm sure it's fine. Although, it seems that the last one was the only one that worked when used in column E.

NBVC, You're an absolute legend, mate!

I've got one little problem though that I can't seem to sort out. On the test example when I copied down the top 5 sum formula (the last of your three examples) when it got to GART2 it was fine. On the spreadsheet I'm actually working on I got an error #NUM. I can't really see that there's any difference. The only thing that's bothering me is that when it changes PF there's no value in LMV. And the values for GART2 are exceptionally large (100,000,000,000.00). Could either of these cause this issue to occur?

I know you most likely hear this a million times, but I have triple checked that I have changed the cells correctly and I have as the first calc works perfectly.

Very odd. Any ideas? I'd love to post the actual spreadsheet on here for you but it has somewhat sensitive data, is there any way I can post it on here and then delete it after you've viewed it, or send it only to you???

Thanks so much, you've helped me out no end and I was racking my brains trying to find this solution!

I've figured it out! On my actual workbook there's not 5 LMV's or SMV's so the end of the formula ...{1,2,3,4,5} needed to be changed to the exact amount of LMV's that there are.

Can you think of any possible ways to get around this? I'm honestly happy for it to be like this but if you could find a solution to that, you'd be awesome!

Cheers,
Scott.

Looks like you beat me to that too!!

Oddly enough, the first one you did for the marking was perfect and didn't need amending!

This last formula is magnificent - I have absolutely no idea how it works, but it's works perfectly. Doesn't get rid of the zero values, but I have no care with those anyway.

Thanks ever so much. I've hit the scales as many times as this will let me.