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

Question

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

Sheet 2

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:

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

Answer 1

Update as per present edited post by OP:

Try the following formula:

---

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

---

---

• 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()

---

• 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,

---

• 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,

---

• 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

---

---

• 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

---

Excel can be downloaded from here

---

As per comments of OP:

The error occurs when column names have special characters like V2_AB

---

=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))

---