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
Recent Comments