Forum Discussion

vedantaher's avatar
vedantaher
Copper Contributor
Sep 04, 2023

Group by in excel

I have multiple values for each date of Jan. month and i need total of values for each date how can i go about this is there any other option than writing power query? 
Excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 04, 2023

vedantaher 

For a small, fixed number of 'Type' columns you could aggregate them individually using a dynamic array formula

= LET(
    distinctDates, UNIQUE(Table1[Date]),
    type1Grouped,  SUMIFS(Table1[Type1], Table1[Date], distinctDates),
    type2Grouped,  SUMIFS(Table1[Type2], Table1[Date], distinctDates),
    HSTACK(distinctDates, type1Grouped + type2Grouped)
  )

The version with the grand total and the conditional formatting requires a bit more effort.

= LET(
    distinctDates, UNIQUE(Table1[Date]),
    type1Grouped,  SUMIFS(Table1[Type1], Table1[Date], distinctDates),
    type2Grouped,  SUMIFS(Table1[Type2], Table1[Date], distinctDates),
    grouped,       HSTACK(distinctDates, type1Grouped + type2Grouped),
    grandTotal,    HSTACK("Grand Total", SUM(type1Grouped, type2Grouped)),
    VSTACK(grouped, grandTotal)
  )