Search code examples
excelsortingexcel-formulahstack

Rank categories in column based on sum of values in another column in excel

Let's say there are 4 columns in Sheet 1 with col1 as categories and col2, col3, and col4 are value columns as shown below. In sheet 2, we want to obtain top 2 categories highest sum of values in the selected variable column.

Sheet 1

enter image description here

Sheet 2

enter image description here

I have used the following code to find top values for the table not for each categories, any suggestions to revise this?

=TAKE(SORT(HSTACK(INDEX(Sheet1!B2:D11,,MATCH(H1,Sheet1!B1:D1,0)),CHOOSECOLS(Sheet1!A2:D11,1)),1,-1),5)

Desired output if VAR1 is in user input:

enter image description here

Note: The formulas should not be sensitive to column names in sheet1.

Solution

  • Update as per present edited post by OP:

    Try the following formula:

    enter image description here

    =HSTACK({1;2},
  TAKE(SORT(UNIQUE(HSTACK(SUMIFS(INDEX(Sheet1!B2:D11,,XMATCH(H1,Sheet1!B1:D1)),
  Sheet1!A2:A11,Sheet1!A2:A11),Sheet1!A2:A11)),1,-1),2))

    Or,

    =LET(
     a, Sheet1!A2:A11,
     HSTACK({1;2},TAKE(SORT(UNIQUE(HSTACK(SUMIFS(
     XLOOKUP(H1,Sheet1!B1:D1,Sheet1!B2:D11),a,a),a)),1,-1),2))

    Or,

    =LET(
     a, Sheet1!A2:A11,
     HSTACK({1;2},TAKE(SORT(UNIQUE(HSTACK(
     SUMIFS(INDEX(Sheet1!B2:D11,,XMATCH(H1,Sheet1!B1:D1)),a,a),a)),1,-1),2)))

    Solutions shown below are referring to the original post of OP, which is a working solution. However, OP had special characters in header name on sheet1, which was not mentioned on the post.

    enter image description here

    • Formula used in cell D2

    =LET(
     a, UNIQUE(A2:A12),
     b, TAKE(SORT(HSTACK(a,SUMIFS(B2:B12,A2:A12,a)),2,-1),2),
     VSTACK({"Rank","V1","V2"},HSTACK({1;2},b)))

    You can also use the MMULT() in place of SUMIFS(), MMULT() is more resource heavy than SUMIFS, so the latter will be simpler and more efficient, still shared the approach with MMULT()

    enter image description here

    • Formula used in cell D2

    =LET(
     a, UNIQUE(A2:A12),
     b, TAKE(SORT(HSTACK(a,MMULT(N(TOROW(A2:A12)=a),B2:B12)),2,-1),2),
     VSTACK({"Rank","V1","V2"},HSTACK({1;2},b)))

    Or,

    enter image description here

    • Formula used in cell D2

    =HSTACK({1;2},
 TAKE(SORT(UNIQUE(HSTACK(A2:A12,
 SUMIFS(B2:B12,A2:A12,A2:A12))),2,-1),2))

    Or,

    enter image description here

    • Formula used in cell D2

    =HSTACK({"1";2},
 TAKE(SORT(UNIQUE(HSTACK(A2:A12,
 MMULT(N(A2:A12=TOROW(A2:A12)),B2:B12))),2,-1),2))

    Edited :

    As mentioned by OP in comments below:

    As shown in example can we take if the column to be considered for sorting as a user input as is given in cell E2 of desired output

    enter image description here

    • Formula used in cell D2

    =HSTACK({1;2},
 TAKE(SORT(UNIQUE(HSTACK(A2:A12, 
 SUMIFS(B2:B12,A2:A12,A2:A12))),
 XMATCH(D1,A1:B1),-1),2))

    Update:

    OP mentions in comments:

    This code gives error if the data is in another sheet in the same workbook. The formula used example reads data from another sheet

    enter image description here

    Excel can be downloaded from here

    As per comments of OP:

    The error occurs when column names have special characters like V2_AB

    enter image description here

    =HSTACK({1;2},TAKE(SORT(UNIQUE(HSTACK(Sheet1!$A$2:$A$12,
 SUMIFS(Sheet1!$B$2:$B$12,Sheet1!$A$2:$A$12,Sheet1!$A$2:$A$12))),
 XMATCH(E1,TEXTBEFORE(Sheet1!$A$1:$B$1,"_",,,,Sheet1!$A$1:$B$1)),-1),2))