Highest number totalling?

Hello everyone.

I'm fairly new to the programming side of Excel, but am slowly picking it up!

I have a question, hopefully you will be able to help. I need to know how I can create a formula which will sum a selection of the highest numbers in a range of data. So for example, if I have a row of 10 cells each containing dfferent numbers, and I want to take the 7 highest values from there and get the sum of these values, how do I go about this?

I've tried using the LARGE command but can only get an individual figure, can someone please point me in the right direction?

Thanks all,

Roy K.

assumption:

cell a1 to a10 contain 1 to 10
(for example: a1 contain 1, a2 contain 2, a3 contain 3, and so on)

cell b1 contain: any number from 1 to 9



try this in cell c1:

=SUMIF(A1:A10,">"&LARGE(A1:A10,B1+1))

Hi you were on the right track with LARGE bbut you need an array formula like this

=SUM(LARGE(A3:L3,{1,2,3,4,5,6,7}))

lets say your data is on row 3 from column A to L and you want to calculate the top 7 amounts then this will do it paste this formula in to any cell outside of your data range change the A3:L3 to be your range of data, the list of figures you see i.e 1 - 7 thats where you set the amount of top figures you want to calculate,.....anyway once you have changed what you need before you leave the cell you must press and hold Ctrl, Shift and then press enter, now let go of the other buttons, the action you have just performed is to create an array formula. You can substitute the word LARGE for SMALL if you wanted the lowest figures, anyway have a play around with it!

Regards,
SImon

Hello Simon,

A formula like

=SUM(LARGE(A3:L3,{1,2,3,4,5,6,7}))

doesn't require CTRL+SHIFT+ENTER

note: if A3:L3 may contain less than 7 values the above will give an error. If you want to sum the largest 7 or all numbers if there are less than 7

=SUM(LARGE(A3:L3,ROW(INDIRECT("1:"&MIN(7,COUNT(A3:L3))))))

....this one does require CTRL+SHIFT+ENTER

Cheers, thanks for that!, i guess you are the Daddy! , hope you dont mind if i retain that formula for future use?

Regards,
Simon

Gentleman,

First up thanks for all the replies, will have to look into array formulas as I have not even tinkered with them yet!

As the number of figures to go into the sum comes from another cell (from a lookup table) it seems to be easier to use Baraks formula, as its easier to incorporate this number. Can anyone see why his solution wouldnt be suitable?

Thanks again,

Roy K.

Originally Posted by creationracing

Can anyone see why his solution wouldnt be suitable?

It depends on whether you might have duplicates, and what you might want the result to be in that case

Take an extreme case, B1 contains 7 so you want the sum of the 7 largest numbers in A1:A10, if A1:a10 contains the following numbers

6, 7, 8, 8, 8, 8, 8, 8, 8, 8, 8

then

=SUMIF(A1:A10,">"&LARGE(A1:A10,B1+1))

will give you a result of 0

=SUM(LARGE(A1:A10,{1,2,3,4,5,6,7}))

will give you 56