Adding Up Tops and Bottoms

Alternative formulae which are less cumbersome to write with larger N would be

=SUM(LARGE($C$5:$C$25,{1,2,3}))
and
=SUM(SMALL($C$5:$C$25,{1,2,3}))

To change the number of values summed, just add or subtract from the {1,2,3}
(Unlike an array formula, the curly braces have to be typed in from the keyboard and there is no need to use Ctrl-Shift-Enter)

Alternative formulae which are less cumbersome to write with larger N would be

=SUM(LARGE($C$5:$C$25,{1,2,3}))
and
=SUM(SMALL($C$5:$C$25,{1,2,3}))

To change the number of values summed, just add or subtract from the {1,2,3}
(Unlike an array formula, the curly braces have to be typed in from the keyboard and there is no need to use Ctrl-Shift-Enter)

Thanks Dave

Hi Linda,

Yes, this works fine for duplicates. Try this small example for test or experimentation...

In a blank worksheet, I entered the following twelve values in Cells A4 to A15:
21, 18, 20, 21, 17, 5, 16, 10, 2, 6, 17, 5

To the right, in Column C, I entered the formula
=LARGE($A$4:$A$15,1)
This returns the value 21.

Drag it down two cells and change the parameter value to 2 and 3 respectively.
You should get a second 21 and a 20. So, duplicates are handled no problem. The same applies if you replace LARGE by SMALL.

To simplify the addition element, you can incorporate the task into a shorter function, rather than adding the three LARGE formulae results.

In a blank cell, enter the formula:
=SUMIF(A4:A15,">=" & LARGE(A4:A15,3))

In my example you should get a result of 62. Excel evaluates the LARGE function to find the third largest value, in this case 20. The SUMIF then adds any values greater-than-or-equal-to this value.

NOTE:
If you have, for example, five cells all with the value 21, then the SUMIF formula will return a misleading value of 105. You will then need to stay with the sum of the three LARGE formulae to get the correct answer. My SUMIF works very well with unique data values.

Allen's tip is especially useful if you are not in a position to sort the data, e.g. to help identify duplicates.

I hope I haven't confused you!

Can this formula handle ties or duplicate values.