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? 

  • vedantaher 

    For an arbitrary number of 'Type's you could use

    = LET(
        distinctDates, UNIQUE(Table1[Date]),
        totalsByDate,  BYROW(distinctDates, 
          LAMBDA(date,
            SUM(FILTER(DROP(Table1,,1), Table1[Date] = date))
          )
        ),
        HSTACK(distinctDates, totalsByDate)
      )

     

    • vedantaher's avatar
      vedantaher
      Copper Contributor

      I did thing is i want to multiply type1 and type2 and then do the sum for that date but pivot is giving me sum of Type1 and sum of type 2 which is not desirable

      Date	Type1	Type2	result
      01-08-2023	z	y	
      01-08-2023	a	g	
      02-08-2023	b	h	



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

     

     

     

Share