Forum Discussion
vedantaher
Sep 04, 2023Copper Contributor
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?
- PeterBartholomew1Silver Contributor
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) )
- Riny_van_EekelenPlatinum Contributor
vedantaher Use a pivot table!
- vedantaherCopper 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
- Riny_van_EekelenPlatinum Contributor
vedantaher Sorry, I don't understand.
- PeterBartholomew1Silver Contributor
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) )