Excel sum/max-min multiple rows, insert new row and delete added rows

Question

I have the Excel data shown below. Column A has the ID, columns B and C have segments of values associated with the ID, and Columns D and E are general descriptions of the ID (associated with the ID, not individual segments). The data has about 600 IDs and is about 1000 rows long.

ID	Value 1	Value 2	Descr 1	Descr 2
11	2.5	1.8	a	x1
11	2.3	1.1	a	x1
11	1.9	1.6	a	x1
12	3.7	3.5	b	x2
12	3.9	1.5	b	x2
13	2.5	0.2	c	x3
13	2.6	4.1	c	x3
13	2	4.8	c	x3
13	2.7	1.8	c	x3

I am trying to create a single row for each ID. This row will add up values in columns B and C, and delete repeated descriptions, wherever the ID matches in column A.

I have tried using SUMIF, but I can't figure out how to automate that over the entire row of data. I believe there should be a better function/method to do this. Expected is a table with unique ID, values, and description.

ID	Value 1	Value 2	Descr 1	Descr 2
11	6.7	4.5	a	x1
12	7.6	5.0	b	x2
13	9.8	10.9	c	x3

---

Edited

---

@Ike Solution below worked for me. I was trying to get max value 1 and min value 2 for each ID. I edited Ike Solution to below, but it threw up a #Value! error

=LET(data,A1:E10,
       uIDs, UNIQUE(CHOOSECOLS(data,1,4,5)),
       aValues, MAKEARRAY(ROWS(uIDs), 1, LAMBDA(r,c, MIN(IF(INDEX(data,,1),INDEX(uIDs,r,1),INDEX(data,,c+1))))
                                                    ),
       bValues, MAKEARRAY(ROWS(uIDs), 1, LAMBDA(j,k, MAX(IF(INDEX(data,,1),INDEX(uIDs,j,1),INDEX(data,,k+1))))
                                                    ),
      CHOOSECOLS(HSTACK(uIDs,aValues, bValues),1,4,5,2,3))

Answer 1

If applicable one could use GROUPBY() works with MS365 for Beta Versions.

---

=CHOOSECOLS(GROUPBY(HSTACK(A1:A10,D1:E10),B1:C10,SUM,3,0),1,4,5,2,3)

---

MSFT Documentations: GROUPBY or PIVOTBY

---

Edit: As per the new update by OP

---

=CHOOSECOLS(DROP(GROUPBY(HSTACK(A1:A10,D1:E10),B1:C10,HSTACK(MAX,MIN),3,0),1),1,4,5,2,3)

---

Using One LAMBDA() Helper Function MAKEARRAY() with AGGREGATE() function to get the desired output of MIN & MAX values:

---

=LET(
     _Data,A2:E10,
     _Uniq, UNIQUE(CHOOSECOLS(_Data,1,4,5)),
     _MinMax, MAKEARRAY(ROWS(_Uniq),2,LAMBDA(r,c,
              INDEX(AGGREGATE({15,14},6,
              INDEX(HSTACK(INDEX(_Data,,2),INDEX(_Data,,3)),,c)/
              (INDEX(_Data,,1)=INDEX(_Uniq,r,1)),1),c))),
     _Answer, CHOOSECOLS(HSTACK(_Uniq,_MinMax),1,4,5,2,3),
     VSTACK({"ID","Min","Max","Descr 1","Descr 2"},_Answer))

---

Also for MAX and MIN one needs to interchange the function_num in the AGGREGATE() function:

---

Test Case:

---

Edit: Forgot to use CHOOSECOLS() so here is an updated version:

=LET(
     _Data,A2:E19,
     _Uniq, UNIQUE(CHOOSECOLS(_Data,1,4,5)),
     _MinMax, MAKEARRAY(ROWS(_Uniq),2,LAMBDA(r,c,
              INDEX(AGGREGATE({15,14},6,
              INDEX(CHOOSECOLS(_Data,2,3),,c)/
              (INDEX(_Data,,1)=INDEX(_Uniq,r,1)),1),c))),
     _Answer, CHOOSECOLS(HSTACK(_Uniq,_MinMax),1,4,5,2,3),
     VSTACK({"ID","Min","Max","Descr 1","Descr 2"},_Answer))

---

Answer 2

you can use pivottable functionality along with formulas like SUMIF to summarize the data

Answer 3

Try this formula:

=LET(data,A2:E10,
uIDs, UNIQUE(CHOOSECOLS(data,1,4,5)),
sValues,MAKEARRAY(ROWS(uIDs),2,LAMBDA(r,c,
                     SUMIF(INDEX(data,,1),INDEX(uIDs,r,1),INDEX(data,,c+1)))),
CHOOSECOLS(HSTACK(uIDs,sValues),1,4,5,2,3))

sValues creates the sums per unique ID.

Answer 4

After some reading, figured out how to use the maxifs and minifs function. Solved it with the code below (an edit of Ike's answer):

=LET(data,A2:A10,
       uIDs, UNIQUE(CHOOSECOLS(data,1,4,5)),
       startValues, MAKEARRAY(ROWS(uIDs), 1, LAMBDA(r,c, MINIFS(INDEX(data,,c+1),  INDEX(data,,1), INDEX(uIDs,r,1)))
                                                    ),
       endValues, MAKEARRAY(ROWS(uIDs), 1, LAMBDA(j,k, MAXIFS(INDEX(data,,k+2),  INDEX(data,,1), INDEX(uIDs,j,1)))
                                                    ),
      CHOOSECOLS(HSTACK(uIDs,startValues, endValues),1,2,3,4,5))