# Group by in excel

Copper 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?

9 Replies

# Re: Group by in excel

@vedantaher Use a pivot table!

# Re: Group by in excel

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

# Re: Group by in excel

@vedantaher Sorry, I don't understand.

# Re: Group by in excel

Perhaps you can use SUMPRODUCT

=SUMPRODUCT(--(DateColumn=Date), Type1Column, Type2Column)

# Re: Group by in excel

my input

Date	type1	type2
01-08-2023	2	3
01-08-2023	1	2

pivot output and my result accordingly

Date	   type1     type2  	Result
01-08-2023	3	  5	   15

what i want

Date	   Result
01-08-2023	8

# Re: Group by in excel

You could create a calculated field in a pivot table:

# Re: Group by in excel

@vedantaher Create a calculated Field like this:

and then a pivot table like this:

file attached.

# Re: Group by in excel

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

# Re: Group by in excel

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