HOW TO SUM THE TOP or BOTTOM n VALUES; 5 Methods

Learning how to sum the top or bottom n values in excel requires one to first be familiar with the LARGE & SMALL functions.

The LARGE function returns the “nth largest” Value in a list…=LARGE( list, n)

The SMALL function returns the “nth smallest” Value in a list…=SMALL( list, n)

Using SUMIFS Function

=SUMIFS(tbl[Sales],tbl[Sales],”>=”&LARGE(tbl[Sales],H3))

To Sum for the bottom values in above example, Change the operator to “Less than or equal to” and replace LARGE function with SMALL function

=SUMIFS(tbl[Sales],tbl[Sales],”<=”&SMALL(tbl[Sales],H4))

Using SUM Function

=SUM((tbl[Sales]>=LARGE(tbl[Sales],H3))*tbl[Sales])

How it works:

(tbl[Sales]>=LARGE(tbl[Sales],H3)) compares the sales values with the 10th largest sales value and returns an array of TRUE (1) and FALSE (0)

=SUM(tbl[Sales]*{TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE})

When you multiply this with the sales value, it returns only values greater than the 10th

=SUM({283;266;16066;0;497;261;1474;248;0;241;327;5753;0;0;0})

SUM function adds up these values

For the Bottom values, change the operator to “less or equal to” (<= ) and LARGE function to SMALL function

NB: You can alternatively interchange SUM function with SUMPRODUCT

=SUMPRODUCT((tbl[Sales]>=LARGE(tbl[Sales],H3))*tbl[Sales])

Using SUMPRODUCT & SEQUENCE Function

For those with Office365 you can combine SUMPRODUCT, LARGE and the new dynamic function SEQUENCE

=SUMPRODUCT(LARGE(tbl[Sales],SEQUENCE(H3)))

Using SUMPRODUCT & ROW Function

If you do not have office365, you can use the ROW Function as a substitute for SEQUENCE

=SUMPRODUCT(LARGE(tbl[Sales],ROW(1:10)))

=SUMPRODUCT(LARGE(tbl[Sales],ROW(1:10)))

Using SUMPRODUCT & RANK Function

RANK Function returns the position of a numeric value when compared to a list of other numeric values.

=SUMPRODUCT((RANK(tbl[Sales],tbl[Sales])<=I3)*tbl[Sales])

Summing the botton values using RANK, you need to change the default ordering order from Descending (0) to Ascending (1)

=SUMPRODUCT((RANK(tbl[Sales],tbl[Sales],1)<=3)*tbl[Sales])…this function will sum the bottom 3 sales values

DOWNLOAD WORKBOOK

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.