Group by in excel

Copper Contributor

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

@vedantaher Use a pivot table!

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 Sorry, I don't understand.

@vedantaher 

Perhaps you can use SUMPRODUCT

 

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

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

 

@Riny_van_Eekelen 

@vedantaher 

You could create a calculated field in a pivot table:

@vedantaher Create a calculated Field like this:

Riny_van_Eekelen_0-1693820866777.png

and then a pivot table like this:

Riny_van_Eekelen_1-1693820906764.png

file attached.

 

 

 

@vedantaher 

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

image.png

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

 

 

 

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