excelsummarysumifsmaxifs

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

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

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

    enter image description here

    =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

    enter image description here

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

    enter image description here

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

    enter image description here

    Test Case:

    enter image description here

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