Sum multiple column values as per multiple criteria using SUMPRODUCT or any other array function

Question

Date	Dealer ID	East Region	South Region	West Region	North Region	Brand
05/02/2024	CareAuto-SKU2	350	120	560	200	MARUTHI
16/03/2024	ANG.CO-BHK6	200	185	450	75	KIA
15/02/2024	RajMot-RFQ11	600	150	600	45	TOYOTA
20/01/2024	RIDE.KM-T9	150	225	450	120	KIA
01/04/2024	ANG.CO-BHK6	75	100	300	100	MARUTHI
30/01/2024	RajMot-RFQ11	630	65	200	300	KIA
28/02/2024	CareAuto-SKU2	420	55	150	450	TOYOTA
26/03/2024	ANG.CO-BHK6	100	75	90	250	KIA

Above data formatted as Table1. How to sum above table values month wise, Dealer code wise, Brand wise, Region wise.

Initial task: Create 3 drop down lists >> 1.unique month names in "mmm-yyyy" format. 2. unique Dealer codes from Col. B range using text after last deli meter "-". 3. unique Brand names.

Next task: Add all 3 drop down lists in Col. H3:J3 required formula results in K3:N3.

Final task: how to get results using SUMPRODUCT or any other latest array formula. If sum values not found make result cell value as empty "" .Sample result as below.

Col. H	Col. I	Col. J	Col. K	Col. L	Col. M	Col. N
Month (DD list1)	Dealer Code (DD list2)	Brand (DD list3)	East Region	South Region	West Region	North Region
---	---	---	---	---	---	---
Mar-2024	BHK6	KIA	300	260	540	325

Answer 1

Hi,

In cell A12, enter this formula

=GROUPBY(HSTACK(TEXT(A2:A9,"mm-yyyy"),TEXTAFTER(B2:B9,"-"),G2:G9),C2:F9,SUM,,0)

This will bring over the result of all unique combinations at once.

Answer 2

In K3:

=LET(s, SUMIFS(Table1[East Region], Table1[[Date]:[Date]], ">="&$H3, Table1[[Date]:[Date]], "<="&EOMONTH($H3, 0), Table1[[Dealer ID]:[Dealer ID]], "*-"&$I3, Table1[[Brand]:[Brand]], $J3), IF(s=0, "", s))

Fill to the right to N3

Answer 3

Hi tkrajutkraju,

Thanks for your post in Microsoft Community.

First task: Firstly, we need to create the data source for the dropdown lists. For example, create a new Sheet2. 

In cell A1, enter this formula to format the date in column A of the original data. 

=UNIQUE(TEXT(Sheet1!A2:A9,"mmm-yyyy")) 

In cell B1, enter this formula to extract the Dealer IDs: 

=RIGHT(Sheet1!B2:B9, LEN(Sheet1!B2:B9)-FIND("-",Sheet1!B2:B9)) 

In cell C1, enter the following formula to get unique Brand names.

=UNIQUE(Sheet1!G2:G9)

Second task: Create dropdown lists in cells H2 to J2, referencing the data sources created in Sheet2.

Third task: Using the SUMPRODUCT function might be difficult. I recommend using the FILTER function. Enter this formula in K2: =FILTER(C2:F9, (TEXT(A2:A9,"mmm-yyyy")=H2)(RIGHT(B2:B9, LEN(B2:B9)-FIND("-",B2:B9))=I2)(G2:G9=J2), "")

I hope the information above is helpful to you, and I look forward to your reply!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist

Answer 4

Thank You Ashish Ji, this is the best solution formula. Thank you.

Answer 5

Sorry to inform you , I haven't got desired results. I think you haven't gone through my question thoroughly. As per my sample data your formula is not summing the column values.