3 Easy Ways to Sum Top n Values in Excel

SUM Top n Values Using SUMIF and LARGE Functions

Today I will be showing how you can sum any top n values from a data set in Microsoft Excel.


Download Practice Workbook


3 Easy Ways to Sum Top n Values in Excel

First of all, let us have a look at our data set. We have a sales record of two months of a company named Nicholas Group.

A Data Set in Excel

Now we will try to sum any top n values from the sales.

First, let’s try to sum up the top 5 sales from the month of January.


1. Use SUMIF Function to Sum Top n Values in Excel

We can accomplish the task using the SUMIF function of Excel along with the LARGE function.

The formula that we will use here is:

=SUMIF(C4:C18,">="&LARGE(C4:C18,5))

SUM Top n Values Using SUMIF and LARGE Functions

See, we have got the sum of top 5 sales from the month of January, $150,000.00

Now for better understanding, let’s break down the formula.

  • The LARGE function takes a range of cells and a parameter ‘k’ as arguments and returns the kth largest value from that range of cells.
  • So LARGE(C4:C18,5) returns the 5th largest value from the cells C4 to C18. In this case, it is $25000.00.

LARGE Function in Excel

  • The SUMIF function takes a range of cells and a criteria and returns the sum of the cells from the range which maintain the criteria.
  • So SUMIF(C4:C18,">="&LARGE(C4:C18,5)) returns the sum of the cells from C4 to C18 which contain values greater than or equal to LARGE(C4:C18,5), or the 5th largest value from the cells C4 to C18.
  • This is the sum of the top 5 values from the cells C4 to C18, that is, the sales in the month of January.

2. Use SUM Function to Sum Top n Values in Excel

We can also calculate the sum of the top 5 sales of the month of January using the SUM function of Excel.


Option 1: SUM with IF and RANK Function

You can execute the task using the SUM, IF, and RANK functions of Excel.

Select a cell, enter this formula and press Ctrl + Shift + Enter (As this is an Array Formula):

=SUM(IF(RANK(C4:C18,C4:C18)<=5,C4:C18,0))

SUM Top n Values Using SUM, IF and RANK Functions

See, we have got the same result as earlier, $150,000.00

Now for better understanding, let’s break down the formula.

  • The RANK function takes a cell reference and a range of cells as the arguments and returns the rank of the cell in the given range of cells in descending order. To know more, visit this link.
  • RANK(C4:C18,C4:C18) takes a range of cell references (C4 to C18) as the argument, instead of a single cell reference. So it returns an array containing the rank of each cell from C4 to C18 in the range of cells C4 to C18.
[Have to press Ctrl + Shift + Array and drag the Fill Handle to get the complete output]

RANK Function in Excel

  • IF function takes 3 arguments, one criterion and two values. Return the first value if the criterion is TRUE, and return the second value if the criterion is FALSE.
  • IF(RANK(C4:C18,C4:C18)<=5,C4:C18,0) takes an array of criteria (RANK(C4:C18,C4:C18)<=5) in place of a single criterion, and an array of values to be returned if TRUE (C4:C18), and a zero as the value to be returned if FALSE.
  • RANK(C4:C18,C4:C18)<=5 returns {FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}
  • This means it returns a TRUE for each cell between C4 to C18, if it has a value in the top 5 zone, otherwise it returns a FALSE.

Criterion with RANK Function in Excel

  • So IF(RANK(C4:C18,C4:C18)<=5,C4:C18,0) becomes:

=IF({FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}, {C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18}, {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

  • And it returns {0,0,0,0,0,0,25000,28000,30000,0,0,0,0,35000,32000}

IF and RANK Functions in Excel

  • So SUM(IF(RANK(C4:C18,C4:C18)<=5,C4:C18,0)) now becomes: SUM(0,0,0,0,0,0,25000,28000,30000,0,0,0,0,35000,32000) which returns the sum of top 5 sales.

Option 2: SUM with LARGE Function

You can calculate the sum of the top 5 sales using the SUM function with the LARGE function of Excel.

The formula to be used is:

=SUM(LARGE(C4:C18,{1,2,3,4,5}))
[This is also an Array Formula. Press Ctrl + Shift + Enter to execute it].

Sum Top n Values with SUM and LARGE Functions

See, this time we have also got $150,000.00

Now for better understanding, let’s break down the formula again.

  • As mentioned earlier, the LARGE function takes a range of cells and a parameter k as the arguments. And returns the kth largest value from that range of cells.
  • LARGE(C4:C18,{1,2,3,4,5}) 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 respectively, {35000,32000,30000,28000,25000}
  • So SUM(LARGE(C4:C18,{1,2,3,4,5})) becomes SUM(35000,32000,30000,28000,25000) which returns the sum of the top 5 sales.

Option 3: SUM with SEQUENCE Function

Now, think for a moment. You have to calculate the sum of the top 100 values from a data set. Is it possible to enter manually 1,2,3… up to 100 in the previous formula?

That is almost impossible. So what is the way?

No worries. We can still accomplish it using the SUM and the LARGE function of Excel, but this time using one more function, SEQUENCE.

Note: The SEQUENCE function is only available in Office 365.

Let’s try to find out the sum of the top 10 sales from the month of January this time.

The formula that we shall use here will be:

=SUM(LARGE(C4:C18,SEQUENCE(10,1)))

Sum Top n Values with SUM, LARGE and SEQUENCE Functions

See, we have got the sum of top 10 sales, $264,500.00

  • Here, SEQUENCE(10,1) returns an array of values from 1 to 10 {1,2,3,4,5,6,7,8,9,10}.

SEQUENCE Function in Excel

  •  LARGE(C4:C18,SEQUENCE(1,10)) returns the top 10 large sales in the range C4 to C18.

LARGE and SEQUENCE Functions in Excel

  • And finally, SUM(LARGE(C4:C18,SEQUENCE(10,1))) returns the sum of the top 10 sales.

3. Use SUMPRODUCT Function to Sum Top n Values in Excel


Option1: SUMPRODUCT with LARGE, ROW, and INDIRECT Function

In section 2.2, we saw how you can calculate the sum of the top 5 sales using the LARGE and the SUM function.

It is easy for the top 5, but when it comes to large numbers like the top 50 or top 100, this process is quite unuseful.

We achieved the same goal in section 2.3 for top 10 sales, using a combination of SUM, LARGE, and SEQUENCE functions. But the SEQUENCE function is only available in Office 365.

Now, we will again achieve the same goal, but this time using a combination of SUMPRODUCT, LARGE, ROW, and INDIRECT functions.

The formula that we shall use here is:

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

SUM Top n Values Using SUMPRODUCT, LARGE, ROW and INDIRECT Functions

See, we have got the same result as earlier, $264,500.00

  • Here ROW(INDIRECT("1:10")) returns an array of values from 1 to 10 {1,2,3,4,5,6,7,8,9,10}.

ROW and INDIRECT Functions in Excel

  • LARGE(C4:C18,ROW(INDIRECT("1:10"))) returns the top 10 large values (Same as section 2.2 and 2.3)
  • SUMPRODUCT(LARGE(C4:C18,ROW(INDIRECT("1:10")))) returns the sum of the top 10 large values.


Option 2: SUMPRODUCT with RANK Function

Let’s try to find out the sum of the top 10 sales again, this time using SUMPRODUCT along with the RANK function of Excel.

The formula will be:

=SUMPRODUCT(C4:C18,--(RANK(C4:C18,C4:C18)<=10))

Sum Top n Values Using SUMPRODUCT and RANK Functions of Excel

See, we got the same result, $264,500.00

  • Here (RANK(C4:C18,C4:C18)<=10)returns an array of TRUE or FALSE. For each cell in the range C4 to C18 which falls under the top 10 it returns a TRUE, and FALSE for the rest.

A Criterion of RANK Function in Excel

  • ‘–‘ before RANK(C4:C18,C4:C18)<=10 converts it an array of 1 and 0 from an array of TRUE and FALSE.

Boolean Values Converted to Numbers in Excel

  • Finally SUMPRODUCT(C4:C18,--(RANK(C4:C18,C4:C18)<=10)) becomes (C4*0 + C5*0 + C6*1 + …. + C18*1). Therefore, it returns the sum of the top 10 sales.

Sum Top n Values in Excel with Criteria

Let’s try something different now.

Try to find out the sum of any top n values, but with a criterion.

Let’s try to calculate the sum of top 10 sales in the month of January, but we will consider only the sales which are less than $30,000.00

How can you do that?
Very easy. Just multiply the criterion inside any of the SUMPRODUCT formulas of section 3.

=SUMPRODUCT(C4:C18,(--(RANK(C4:C18,C4:C18)<=10))*(--(C4:C18<30000)))

Sum Top n Values with Criteria in Excel

See, the sum of the top 10 sales in January less than $30,000.00 is $167,000.00

Note: Here C4:C18 is the range of values from which I extracted the top 10, you use your one.

And if you want, 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"))))

Sum Top n Values in Excel with Texts Inside

Now come to the month of February. Let’s try to find out the top 10  sales from the month of February.

Obviously, we can use any formula from the earlier sections. But one problem is that, in a few cells in the February column, there is a text “No Sales”.

And when we use the formulas, it will show errors. So, what to do?

No worries. Just wrap the LARGE portion from any of the above formulas within an IFERROR function.

=SUM(IFERROR(LARGE(D4:D18,ROW(INDIRECT("1:10"))),0))

Sum Top n Values with Texts Inside in Excel

  • Do not forget to press Ctrl + Shift + Enter as this is an Array Formula.
  • You can use any other formula from section 2, just wrap the LARGE portion within an IFERROR function.

Conclusion

Using these methods, you can calculate the sum of any top n values in Excel pretty conveniently. Do you know of any other method? Let us know in the comment section.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo