sum top values in a range

  • Thread starter Thread starter Jonathan Parminter
  • Start date Start date
J

Jonathan Parminter

Hi, in a range of 10 cells I want to sum the top 6 values.
Is there a formula that will return the 6th top value?

I then want to use the calculated value:
- for conditional formating to highlight the cell
containing the top 6 values in the range.
- for a sumif(A2:K2,">=" & M2). M2 displaying the 6th top
value.

Any suggestions or ideas appreciated :-)

Cheers
Jonathan
 
Try the Large command.

=Large(array,k)

where array is the list of numbers and k the the nth largest term yo
want.

Hope this helps.

Dav
 
There is a function called LARGE (and one called SMALL which you could use
since you know how many cells u have - 10). Since you are familiar with
SUMIF, try a formula which uses SUMIF and LARGE, so that the IF compares the
column being looked at to the 6th largest value (e.g. is greater or equal to
than that number), and SUMS the corresponding data.
 
One way

=SUMPRODUCT(--(LARGE(B1:B10,ROW(INDIRECT("1:6")))))

another

=SUMPRODUCT(--(LARGE(A1:A10,{1;2;3;4;5;6})))
 
=LARGE(Range,6) for only the 6th largest
For the 1-6 highest, select 6 cells in a column and array-enter
(Ctrl+Shift+Enter):
=LARGE(F1:F8,{1;2;3;4;5;6})
 
Back
Top