Sum top n values in Excel

By using the SUMPRODUCT and the LARGE functions, we can sum top n values in Excel. In our example, we will sum top three values from data.

Formula:

How the formula works

The formula used is:

=SUMPRODUCT(LARGE(A2:A7,{1,2,3}))

where,

the syntax for the SUMPRODUCT function is =SUMPRODUCT (array1, [array2], …) and the LARGE function is used against array1 argument or to clarify,

array1 of the SUMPRODUCT function= LARGE(A2:A7,{1,2,3})

array (A2:A7) – The range of cells from which you want the sum of three largest values,

k {1,2,3} – The first three largest values to sum.

Result:

Alternative way to get the same results

We can also use the combination of the SUM and the LARGE functions to sum top n values in Excel. The formula for these combined function is:

=SUM(LARGE(A2:A7,{1,2,3}))

Copyright © 2016 - 2018 Explainry.com | All Rights Reserved