Search code examples
excelexcel-formula

Listing top products based on sales quantity

This is a development of an earlier problem I had posted and accepted an answer for.

I'm looking to sort a list of products based on their total sales, but I wish to include the product AND their total sales in the result table (see image).

I know now (based on a previous answer given to me) how to get the top items, but I'm stuck how to get the sales values too.

enter image description here

Thanks for your help.

Solution

  • Try using the following formula:

    enter image description here

    =LET(
     _Data, A3:B12,
     _Product, TAKE(_Data,,1),
     _Uniq, UNIQUE(_Product),
     TAKE(SORT(HSTACK(_Uniq,MMULT(N(_Uniq=TOROW(_Product)),TAKE(_Data,,-1))),2,-1),5))

    Or, Using BYROW()

    =LET(
     _Data, A3:B12,
     _Product, TAKE(_Data,,1),
     _Uniq, UNIQUE(_Product),
     _TotalSales, BYROW(_Uniq, LAMBDA(x,SUM(--(x=_Product)*TAKE(_Data,,-1)))),
     TAKE(SORT(HSTACK(_Uniq, _TotalSales),2,-1),5))

    Or Using GROUPBY() if applicable:

    =TAKE(GROUPBY(A3:A12,B3:B12,SUM,,0,-2),5)

    Or using SUMIF() or SUMIFS()

    =LET(α, A3:A12, TAKE(UNIQUE(SORT(HSTACK(α,SUMIF(α,α,B3:B12)),2,-1)),5))