Sumproduct with multiple rows and lookup second array column criteria

[clemmer]

I have been trying to solve this for about a month and cannot seem to figured this out. I have two tabs of data that are being referenced to generate an overall final sum of material needed on a dashboard. Currently the only way I can do this is very messy nested sumifs and it breaks when moving across the column

I am looking for the total qty of material within a month, filtered by customer, and multiplied against a percentage that is specific to item and material
The material data is as below, looking to only get the 2nd range(red/orange) as its the calculated fields based off the input in first, by column.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T													AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
3		2022							Units																								Total Sheets (Premium + Regular)
4	Customer	Project Code	PSD	QTY	SKU	Reg. Material /SKU	Prem. Material/SKU	Applique /SKU	Jan-22	Feb-22	####	Apr-22	####	Jun-22	Jul-22	####	####	Oct-22	####	####													JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
5	Customer1	PR1	14-Jan-22	925,000	Item1	1.19	0.02	1.19	925,000	0	0	0	0	0	0	0	0	0	0	0													1,115,729	0	0	0	0	0	0	0	0	0	0	0
6	Customer2	PR1	21-Jan-22	315,408	Item2	1.84	0.42	0.26	315,408	0	0	0	0	0	0	0	0	0	0	0													713,043	0	0	0	0	0	0	0	0	0	0	0
7	Customer3	PR1	#######	378,900	Item3	0.35	0.02	0.01	378,900	0	0	0	0	0	0	0	0	0	0	0													137,036	0	0	0	0	0	0	0	0	0	0	0
8	Customer4	PR2	21-Jan-22	52,560	Item1	1.19	0.02	1.19	52,560	0	0	0	0	0	0	0	0	0	0	0													63,398	0	0	0	0	0	0	0	0	0	0	0
Plan

Cell Formulas
Range	Formula
I5:I8	I5	=IF(AND($C5<=DATE(2022,1,31),$C5>=DATE(2022,1,1)),$D5,)
J5:J8	J5	=IF(AND($C5<=DATE(2022,2,28),$C5>=DATE(2022,2,1)),$D5,)
K5:K8	K5	=IF(AND($C5<=DATE(2022,3,31),$C5>=DATE(2022,3,1)),$D5,)
L5:L8	L5	=IF(AND($C5<=DATE(2022,4,31),$C5>=DATE(2022,4,1)),$D5,)
M5:M8	M5	=IF(AND($C5<=DATE(2022,5,31),$C5>=DATE(2022,5,1)),$D5,)
N5:N8	N5	=IF(AND($C5<=DATE(2022,6,31),$C5>=DATE(2022,6,1)),$D5,)
O5:O8	O5	=IF(AND($C5<=DATE(2022,7,31),$C5>=DATE(2022,7,1)),$D5,)
P5:P8	P5	=IF(AND($C5<=DATE(2022,8,31),$C5>=DATE(2022,8,1)),$D5,)
Q5:Q8	Q5	=IF(AND($C5<=DATE(2022,9,31),$C5>=DATE(2022,9,1)),$D5,)
R5:R8	R5	=IF(AND($C5<=DATE(2022,10,31),$C5>=DATE(2022,10,1)),$D5,)
S5:S8	S5	=IF(AND($C5<=DATE(2022,11,31),$C5>=DATE(2022,11,1)),$D5,)
T5:T8	T5	=IF(AND($C5<=DATE(2022,12,31),$C5>=DATE(2022,12,1)),$D5,)
AG5:AR8	AG5	=+(I5*$F5)+(I5*$G5)

Essentially I need to sum all the quantities in a given column that meet a specific criteria, and then multiply that by the percentage on a table determined via lookup criteria
I have tried multiple sumproducts and index/match to no avail
lookup table

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Item1	Item2	Item3	Item4	Item5	Item6	Item7	Item8	Item9	Item10	Item11	Item12
2	Material1	70.8%	73.9%	8.1%	2.4%	0.0%	70.3%	73.9%	46.7%	81.2%	96.8%	36.1%	69.4%
3	Material2	9.4%	2.4%	25.5%	96.9%	86.7%	9.0%	8.3%	33.2%	10.5%	0.0%	18.1%	8.9%
4	Material3
5	Material4	10.8%	7.7%	46.2%	0.0%	1.8%	11.2%	12.2%	7.1%	2.4%	3.2%	13.9%	15.7%
6	Material5	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.1%	0.0%	0.0%	6.9%	0.4%
7	Material6	4.9%	3.8%	0.0%	0.7%	10.6%	5.7%	5.6%	4.3%	5.9%	0.0%	25.0%	5.2%
8	Material7	0.0%	9.5%	0.0%	0.0%	0.0%	0.0%	0.0%	8.6%	0.0%	0.0%	0.0%	0.0%
9	Material8	4.1%	2.8%	20.2%	0.0%	0.0%	3.6%	0.0%	0.0%	0.0%	0.0%	0.0%	0.4%
10	Material9	0.0%	0.0%	0.0%	0.0%	0.9%		0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
11
12		Item1	Item2	Item3	Item4	Item5	Item6	Item7	Item8	Item9	Item10	Item11	Item12
13	Material11	98%	98%	98%	98%	98.5%	98%	98%	99%	98%			100%
14	Material12	1.1%	1.1%	1.5%	1.5%	1.5%	1.5%	1.1%	0.8%	1.1%	1	1
15	Material13	1.4%	1.4%					1.4%	0.3%
List

Calculation

Example.xlsx
	A	B	C	D
1	Calculation section
2
3			Units
4			JAN	FEB
5	Order 1
6		Material1	105,320.63	-
7		Material2	-	-
8		Material3	120,147.12	-
9		Material4	-	-
10		Material5	54,900.86	-
11		Material6	-	-
12		Material7	45,601.24	-
13		Material8	-	-
Overview

Cell Formulas
Range	Formula
C6:D13	C6	=SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item1")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item1"},cust1[#Headers],0)) +SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item2")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item2"},cust1[#Headers],0)) +SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item3")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item3"},cust1[#Headers],0)) +SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item4")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item4"},cust1[#Headers],0)) +SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item5")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item5"},cust1[#Headers],0)) +SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item6")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item6"},cust1[#Headers],0)) +SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item7")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item7"},cust1[#Headers],0)) +SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item8")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item8"},cust1[#Headers],0)) +SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item9")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item9"},cust1[#Headers],0)) +SUMIFS(Plan!AG:AG,Plan!$A:$A,"customer1",Plan!$E:$E,"item10")*INDEX(cust1,MATCH($B6,List!$A:$A,0),MATCH({"item10"},cust1[#Headers],0))

 

[clemmer]

Check cols F:G in Plan...I can't see them. Can you repost that portion of the Plan sheet?

Then some ranges/formulas weren't quite adjusted correctly. The LET main body formula would be:

=LET( ovwmats, $B$6:$B$18,
           plancust,  Plan!$A$3:$A$13,    planpsd,   Plan!$C$3:$C$13,   plandata,         Plan!$AG$3:$AR$13,  planitems, Plan!$E$3:$E$13,   plandatecolhds, Plan!$AG$2:$AR$2,
           listdata,    List!$B$2:$Z$15,     listmats,   List!$A$2:$A$15,    listdatacolhds, List!$B$1:$Z$1,
           cm,             --(EOMONTH(+planpsd,0)=EOMONTH(C$4,0)),
           items,        FILTER(planitems,cm,""),
           itemamt,   INDEX(FILTER(plandata,cm,""),,XMATCH(C$4,plandatecolhds,0)),
           itemmatp, INDEX(listdata,XMATCH(ovwmats,listmats,0),XMATCH(TRANSPOSE(items),listdatacolhds,0)),
           matamt,    SUMPRODUCT(INDEX(+itemmatp,ROWS(B$6:B6)),TRANSPOSE(itemamt)),
  IFERROR(matamt,""))

I have to step away for a few hours, but will look when I return.

Sure here is that portion

Example (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T													AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1		2022							Units																								Total Sheets (Premium + Regular)
2	Customer	Project Code	PSD	QTY	SKU	Reg. Material /SKU	Prem. Material/SKU	Applique /SKU	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22													Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
3	C1	PR1	1/14/2022	925,000	C1Item1	1.185284091	0.020909091	1.18528409	925000	0	0	0	0	0	0	0	0	0	0	0													1115728.693	0	0	0	0	0	0	0	0	0	0	0
4	C1	PR1	1/21/2022	315,408	C1Item2	1.839657327	0.421044427	0.25701367	315408	0	0	0	0	0	0	0	0	0	0	0													713043.4189	0	0	0	0	0	0	0	0	0	0	0
5	C1	PR1	1/28/2022	378,900	C1Item3	0.345	0.016666667	0.014375	378900	0	0	0	0	0	0	0	0	0	0	0													137035.5	0	0	0	0	0	0	0	0	0	0	0
6	C1	PR2	1/21/2022	52,560	C1Item1	1.185284091	0.020909091	1.18528409	52560	0	0	0	0	0	0	0	0	0	0	0													63397.51364	0	0	0	0	0	0	0	0	0	0	0
7	C1	PR1	1/16/2022	52,560	C1Item9	99	0.1	99	52560	0	0	0	0	0	0	0	0	0	0	0													5208696	0	0	0	0	0	0	0	0	0	0	0
8	C1	PR1	1/19/2022	990,000	C1Item7	89	0.2	89	990000	0	0	0	0	0	0	0	0	0	0	0													88308000	0	0	0	0	0	0	0	0	0	0	0
Plan

Cell Formulas
Range	Formula
I3:T8	I3	=(EOMONTH($C3,0)=EOMONTH(I$2,0))*$D3
AG3:AR8	AG3	=I3*($F3+$G3)

 

[KRice]

There were four things I noticed:

1. If you want "All Customers" and do not want the option to select individual customers, then the expression for the "cm" formula is:

   --(EOMONTH(+planpsd,0)=EOMONTH(C$4,0))

   And the contents of cell A3 on the Overview sheet can be deleted as it is not used. But if you want the ability to choose between selecting any individual customer or "All", then the approach described in post #8 addresses that, where the "cm" formula is:

   IF(cust="All",1,plancust=cust)*(EOMONTH(+planpsd,0)=EOMONTH(C$4,0))

   That same post shows how a unique list of customers can be created that includes the option of "All", and when used with Data Validation in a selection cell (such as A3), the user can choose which type of Overview to generate...whether it be for a specific customer or all of them. In your post #9, you had the "cm" formula looking to match a specific customer whose name is in cell A3 (which held "All Customers")...that's what this part of the formula does: (plancust=cust). So that wasn't going to find any customers in the customer list, hence no items, etc.
2. Your column headings on the Plan sheet from U2:AR2 are still showing the last day of the month rather than the first day, which means the "itemamt" formula never finds a match between the month heading on the Overview sheet and the month headings on the Plan sheet...this part of the formula:

   INDEX(FILTER(plandata,cm,""),,XMATCH(C$4,plandatecolhds,0))

   The easiest fix is to change U2:AR2 to the 1st of every month, so every column denoting a month actually contains a date--the 1st of that month. A better option--to make this workbook easier to turnover into subsequent years--is to use a formula tied to a user input cell denoting the year. In the example posted here, I've added formulas to the month headings and reference the "2022" in cell B1 on the Plan sheet. If 2022 is changed to 2023, all of the headings will become Jan-23, Feb-23, etc. I also modified the display format of these date header columns to a Custom date format to show mmm, which displays the first three letters of the month's name (even though the numerical value for the 1st of that month is stored in the cell).
3. The range for the List sheet column headings should be: listdatacolhds, List!$B$1:$Z$1
4. In this example, across all customers in January, there are 6 items to consider (1,2,3,4,7,9). If we look at the List sheet for those items, the material breakouts for each sum to 100%, so there should be no issue. We should be able to sum column AG on Plan sheet (to obtain 174,052,901.13) and find the same total sum across all materials on the Overview sheet. Except we don't. This is because we haven't listed all of the relevant materials that are found within those items. So rather than showing only Materials1-6, I've added 7-8 to the list. As a side note, some items on the List sheet do not sum to 100% over all of their materials. Those items will produce an apparent error if trying to audit the results because the sum across all customers for a given month on the Plan sheet will produce one value, and the sum obtained from the apportionments to materials for those items in the Overview sheet will lead to relatively small apparent errors where items C1Item6, C2Item8, and C2Item9 are concerned. The material percentage breakdowns on the List sheet should be examined to reconcile this issue.

MrExcel_20220512_clemmer.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1		2022							Units												Total Sheets (Premium + Regular)												Total Sheets (Premium + Regular)
2	Customer	Project Code	PSD	QTY	SKU	Reg. Material /SKU	Prem. Material/SKU	Applique /SKU	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	C1	PR1	1/14/2022	925000	C1Item1	1.1852841	0.0209091	1.1852841	925000	0	0	0	0	0	0	0	0	0	0	0	1096387.784	0	0	0	0	0	0	0	0	0	0	0	1115728.693	0	0	0	0	0	0	0	0	0	0	0
4	C1	PR1	1/21/2022	315408	C1Item2	1.8396573	0.4210444	0.2570137	315408	0	0	0	0	0	0	0	0	0	0	0	81064.16885	0	0	0	0	0	0	0	0	0	0	0	713043.4189	0	0	0	0	0	0	0	0	0	0	0
5	C1	PR1	1/28/2022	378900	C1Item3	0.345	0.0166667	0.014375	378900	0	0	0	0	0	0	0	0	0	0	0	5446.6875	0	0	0	0	0	0	0	0	0	0	0	137035.5	0	0	0	0	0	0	0	0	0	0	0
6	C1	PR2	1/21/2022	52560	C1Item1	1.1852841	0.0209091	1.1852841	52560	0	0	0	0	0	0	0	0	0	0	0	62298.53182	0	0	0	0	0	0	0	0	0	0	0	63397.51364	0	0	0	0	0	0	0	0	0	0	0
7	C1	PR1	1/16/2022	52560	C1Item9	99	0.1	99	52560	0	0	0	0	0	0	0	0	0	0	0	5203440	0	0	0	0	0	0	0	0	0	0	0	5208696	0	0	0	0	0	0	0	0	0	0	0
8	C1	PR1	1/19/2022	990000	C1Item7	89	0.2	89	990000	0	0	0	0	0	0	0	0	0	0	0	88110000	0	0	0	0	0	0	0	0	0	0	0	88308000	0	0	0	0	0	0	0	0	0	0	0
9	C1	PR1	1/22/2022	990000	C1Item4	79	0.3	79	990000	0	0	0	0	0	0	0	0	0	0	0	78210000	0	0	0	0	0	0	0	0	0	0	0	78507000	0	0	0	0	0	0	0	0	0	0	0
Plan

Cell Formulas
Range	Formula
I2:AR2	I2	=TRANSPOSE(EDATE(DATE($B$1,1,1),SEQUENCE(12,1,0)))
I3:T9	I3	=(EOMONTH($C3,0)=EOMONTH(I$2,0))*$D3
U3:AF9	U3	=I3*$H3
AG3:AR9	AG3	=I3*($F3+$G3)
Dynamic array formulas.

MrExcel_20220512_clemmer.xlsx
	A	B	C	D	E
1	Calculation section
2
3	All Customers		Units
4			Jan	Feb	Mar
5	Order 1		174,052,901.13	0	0
6		Material1	72714831.21
7		Material2	84092182.16
8		Material3	0.00
9		Material4	11140106.66
10		Material5	35249.77
11		Material6	5907200.82
12		Material7	67807.86
13		Material8	95522.64
OverviewTst

Cell Formulas
Range	Formula
C4:N4	C4	=TRANSPOSE(EDATE(DATE(Plan!B1,1,1),SEQUENCE(12,1,0)))
C5:E5	C5	=SUM(C6:C18)
C6:E13	C6	=LET( ovwmats, $B$6:$B$18, plancust, Plan!$A$3:$A$13, planpsd, Plan!$C$3:$C$13, plandata, Plan!$AG$3:$AR$13, planitems, Plan!$E$3:$E$13, plandatecolhds, Plan!$AG$2:$AR$2, listdata, List!$B$2:$Z$15, listmats, List!$A$2:$A$15, listdatacolhds, List!$B$1:$Z$1, cm, --(EOMONTH(+planpsd,0)=EOMONTH(C$4,0)), items, FILTER(planitems,cm,""), itemamt, INDEX(FILTER(plandata,cm,""),,XMATCH(C$4,plandatecolhds,0)), itemmatp, INDEX(listdata,XMATCH(ovwmats,listmats,0),XMATCH(TRANSPOSE(items),listdatacolhds,0)), matamt, SUMPRODUCT(INDEX(+itemmatp,ROWS(B$6:B6)),TRANSPOSE(itemamt)), IFERROR(matamt,""))
Dynamic array formulas.

 

[KRice]

I should have mentioned in the description of #2 in my last post, if you go through the steps to replace the month headings, when you paste the formula...

=TRANSPOSE(EDATE(DATE($B$1,1,1),SEQUENCE(12,1,0)))

into the January cells, you will obtain a spill error. This is because the formula uses functions that cause multiple results to automatically spill out into adjacent cells. And since Feb, Mar, Apr, etc. are still occupying those adjacent cells, the formula cannot deliver its results. So before pasting or entering these types of dynamic array formulas, clear out the cells where the results are intended to spill. In this case, each of the monthly column headings would be deleted and the formula above would be inserted with a Paste Special > Formula into only the January cells. The Paste Special > Formula option pastes only the formula while leaving the cell's original formatting intact. If you paste the formula in a cell and obtain the spill error, selecting the formula cell will show the spill range needed, and deleting or moving content out of those cells enables the formula to deliver its results.