# 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.

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

## Seeking Microsoft Certification?

No Huff! No Fluff! No Spam!