Forum Discussion

doraimom_'s avatar
doraimom_
Copper Contributor
May 12, 2025

Totalizing value based on 3 column grouping

Hi.

I have a table as shown below:

I wish to generate a new table, with total length, but the problem is the grouping.

The grouping to add length can only occur for rows where the first three values match. 

That means, 

 

  • Column 1: 16
  • Column 2: 4C + E
  • Column 3: Cu XLPE/PVC, 0.6/1kV
  • Column 4: 45 (To be added up)

can only be grouped with rows where the first three columns match exactly the above values.

If the value in any of the first three columns change, that row will become a different group. 

A coloured example below:

The grouping should occur as coloured, adding up the length for each row, and displaying the total length. In the example above, the four purple rows would be merged in one final row, with the length for each one of them being added to generate a total. 

 

Is there any way to do this without macros or any extremely complicated method? 

 

Best regards, 

excel
Formulas and Functions

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 12, 2025
    =GROUPBY(HSTACK(A2:A11,B2:B11,C2:C11),D2:D11,SUM,0,0,1)

    The easiest way should be using the GROUPBY function which is available in Excel for Microsoft365 and Microsoft365 online.

    =LET(unique,UNIQUE(A2:C11),
HSTACK(unique,
MAP(CHOOSECOLS(unique,1),CHOOSECOLS(unique,2),CHOOSECOLS(unique,3),
LAMBDA(csa,core,insulation,SUMIFS(D2:D11,A2:A11,csa,B2:B11,core,C2:C11,insulation)))))

    Alternatively you can use this formula in Microsoft365 and Excel for the web.

     

Resources