How to Sum Top n Values in Excel (3 Suitable Ways)

Formula Breakdown:

• LARGE(C5:C17,5)

It returns the 5th largest value from the cells C5 to C17.
Result: $25000.00

• SUMIF(C5:C17,”>=”&LARGE(C5:C17,5))

It returns the sum of the cells from C5 to C17 which contain values greater than or equal to the previous result.
Result: 150,000.00

Formula Breakdown:

• IF(RANK(C5:C17,C5:C17)<=5,C5:C17,0)

It takes an array of criteria (RANK(C5:C17,C5:C17)<=5) in place of a single criterion, and it returns a TRUE for each cell between C5 to C17, if it has a value in the top 5 zones. Otherwise, it returns a FALSE. When TRUE, it would return the corresponding cell value from C5 to C17, and when FALSE, it would return 0.
Result: {0,0,0,0,0,25000,28000,30000,0,0,0,35000,32000}

• SUM(IF(RANK(C5:C17,C5:C17)<=5,C5:C17,0))

It sums up the values of the resultant array.
Result: $150,000.00

Formula Breakdown:

• LARGE(C5:C17,{1,2,3,4,5})

It takes an array of values {1,2,3,4,5} in place of a single value k. And returns an array containing the 1st, 2nd, 3rd, 4th, and 5th largest values from the C5:C17 range.
Result: {35000,32000,30000,28000,25000}

• SUM(LARGE(C5:C17,{1,2,3,4,5}))

It sums up the previous resultant values.
Result: $150,000.00

Formula Breakdown:

• SEQUENCE(10,1)

It returns an array of values from 1 to 10.
Result: {1,2,3,4,5,6,7,8,9,10}.

• LARGE(C5:C17,SEQUENCE(10,1))

Returns the top 10 large sales in the range of C5 to C17.
Result: {35000,32000,30000,28000,25000,24500,24000,23000,22000,21000}

• SUM(LARGE(C5:C17,SEQUENCE(10,1)))

Sums the previous resultant array.
Result: $264,500.00

Note:

The SEQUENCE function is only available in Office 365.

Formula Breakdown:

• ROW(INDIRECT(“1:10”))

It returns an array of values from 1 to 10.
Result: {1,2,3,4,5,6,7,8,9,10}.

• LARGE(C5:C17,ROW(INDIRECT(“1:10”)))

It returns the top 10 large values in range C5 to C17.
Result: {35000,32000,30000,28000,25000,24500,24000,23000,22000,21000}

• SUMPRODUCT(LARGE(C5:C17,ROW(INDIRECT(“1:10”))))

It returns the sum of the top 10 large values.
Result: $264,500.00

Formula Breakdown:

• –(RANK(C5:C17,C5:C17)<=10)

It returns an array of TRUE or FALSE. For each cell in the range C5 to C17 which falls under the top 10 it returns a TRUE, and FALSE for the rest.‘–‘ converts the TRUE and FALSE array into an array of 1 and 0.
Result: {0,0,1,1,0,1,1,1,1,1,1,1,1}

• SUMPRODUCT(C5:C17,–(RANK(C5:C17,C5:C17)<=10))

It multiplies C5:C17 cell values to the previous resultant array. Therefore, it returns the sum of the top 10 sales.
Result: $264,500.00.

Note: 

You can also use the more complicated formula below:

=SUMPRODUCT(((LARGE(C4:C18,ROW(INDIRECT("1:10"))))<30000)*LARGE(C4:C18,ROW(INDIRECT("1:10"))))

Note:

You can use any other formula from section 2, just wrap the LARGE portion within an IFERROR function.