Return with sum of top 10 cells in a range which has duplicates

Hi everyone,

I got a formula to calculate the sum of largest 10 cells in a specified range:

=IFERROR(SUMPRODUCT(A1:A100,--(A1:A100>=LARGE(A1:A100,10))),"-")

The problem with this formula is that if there are duplicates in the range, then the number is unexpected. For example, if the largest 20 cells all have a value of 5, then the formula will sum up all these 20 cells. But actually I just want to sum up the only 10 cells, even if there are duplicates.

Can you give me a UDF which returns the sum of the top N cells, if I supply the range and N value? For example, SumTopN(Rng1,N). This UDF will always return the exactly largest N cells even if there are duplicates.

It might be interesting too if a simple Excel formula will do the job.

Many thanks, JeteMc and AlKey. Both of you give me very advanced Excel formulas. Thanks a lot.

Just wondering if there is a handy UDF which can also make it work. I guess for a lot of VBA gurus, this is a simple question

I just created a UDF:

Function SumTopN(InRange, N)
    Dim Total As Double
    Dim i As Long
    Total = 0
    For i = 1 To N
        Total = Total + WorksheetFunction.Large(InRange, i)
    Next i
    SumTopN = Total
End Function