Sumproduct with multiple rows and lookup second array column criteria

C

clemmer

New Member
Joined
May 11, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTAGAHAIAJAKALAMANAOAPAQAR
32022UnitsTotal Sheets (Premium + Regular)
4CustomerProject CodePSDQTYSKUReg. Material /SKUPrem. Material/SKUApplique /SKUJan-22Feb-22####Apr-22####Jun-22Jul-22########Oct-22########JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
5Customer1PR114-Jan-22925,000Item11.190.021.19925,000000000000001,115,72900000000000
6Customer2PR121-Jan-22315,408Item21.840.420.26315,40800000000000713,04300000000000
7Customer3PR1#######378,900Item30.350.020.01378,90000000000000137,03600000000000
8Customer4PR221-Jan-2252,560Item11.190.021.1952,5600000000000063,39800000000000
Plan
Cell Formulas
RangeFormula
I5:I8I5=IF(AND($C5<=DATE(2022,1,31),$C5>=DATE(2022,1,1)),$D5,)
J5:J8J5=IF(AND($C5<=DATE(2022,2,28),$C5>=DATE(2022,2,1)),$D5,)
K5:K8K5=IF(AND($C5<=DATE(2022,3,31),$C5>=DATE(2022,3,1)),$D5,)
L5:L8L5=IF(AND($C5<=DATE(2022,4,31),$C5>=DATE(2022,4,1)),$D5,)
M5:M8M5=IF(AND($C5<=DATE(2022,5,31),$C5>=DATE(2022,5,1)),$D5,)
N5:N8N5=IF(AND($C5<=DATE(2022,6,31),$C5>=DATE(2022,6,1)),$D5,)
O5:O8O5=IF(AND($C5<=DATE(2022,7,31),$C5>=DATE(2022,7,1)),$D5,)
P5:P8P5=IF(AND($C5<=DATE(2022,8,31),$C5>=DATE(2022,8,1)),$D5,)
Q5:Q8Q5=IF(AND($C5<=DATE(2022,9,31),$C5>=DATE(2022,9,1)),$D5,)
R5:R8R5=IF(AND($C5<=DATE(2022,10,31),$C5>=DATE(2022,10,1)),$D5,)
S5:S8S5=IF(AND($C5<=DATE(2022,11,31),$C5>=DATE(2022,11,1)),$D5,)
T5:T8T5=IF(AND($C5<=DATE(2022,12,31),$C5>=DATE(2022,12,1)),$D5,)
AG5:AR8AG5=+(I5*$F5)+(I5*$G5)

1652298179443.png

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
ABCDEFGHIJKLM
1Item1Item2Item3Item4Item5Item6Item7Item8Item9Item10Item11Item12
2Material170.8%73.9%8.1%2.4%0.0%70.3%73.9%46.7%81.2%96.8%36.1%69.4%
3Material29.4%2.4%25.5%96.9%86.7%9.0%8.3%33.2%10.5%0.0%18.1%8.9%
4Material3
5Material410.8%7.7%46.2%0.0%1.8%11.2%12.2%7.1%2.4%3.2%13.9%15.7%
6Material50.0%0.0%0.0%0.0%0.0%0.0%0.0%0.1%0.0%0.0%6.9%0.4%
7Material64.9%3.8%0.0%0.7%10.6%5.7%5.6%4.3%5.9%0.0%25.0%5.2%
8Material70.0%9.5%0.0%0.0%0.0%0.0%0.0%8.6%0.0%0.0%0.0%0.0%
9Material84.1%2.8%20.2%0.0%0.0%3.6%0.0%0.0%0.0%0.0%0.0%0.4%
10Material90.0%0.0%0.0%0.0%0.9%0.0%0.0%0.0%0.0%0.0%0.0%
11
12Item1Item2Item3Item4Item5Item6Item7Item8Item9Item10Item11Item12
13Material1198%98%98%98%98.5%98%98%99%98%100%
14Material121.1%1.1%1.5%1.5%1.5%1.5%1.1%0.8%1.1%11
15Material131.4%1.4%1.4%0.3%
List

Calculation
Example.xlsx
ABCD
1Calculation section
2
3Units
4JANFEB
5Order 1
6Material1105,320.63-
7Material2--
8Material3120,147.12-
9Material4--
10Material554,900.86-
11Material6--
12Material745,601.24-
13Material8--
Overview
Cell Formulas
RangeFormula
C6:D13C6=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))
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
I have a few observations and suggestions to make your task easier. Change the month column headings on the Plan sheet to be actual dates and then use a format option to display month names. Here, instead of entering "Jan" for the 1st month, I entered 1/1/2022 and formatted the display to show MMM-YY. By doing this, you can use references to the month headings in formulas, which means you don't have to hardcode the dates in your formulas. This simplifies the Plan sheet so that you have only two formulas that automatically adjust as the formula is copied throughout the table body.
MrExcel_20220511a.xlsx
ABCDEFGHIJKLMNOPQRSTAGAHAIAJAKALAMANAOAPAQAR
32022UnitsTotals
4CustomerProject CodePSDQTYSKUReg. Material /SKUPrem. Material/SKUApplique /SKUJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22
5Customer1PR114-Jan-22925,000Item11.190.021.19925,000-----------1,115,728.69-----------
6Customer2PR121-Jan-22315,408Item21.840.420.26315,408-----------713,043.42-----------
7Customer3PR128-Jan-22378,900Item30.350.020.01378,900-----------137,035.50-----------
8Customer4PR221-Jan-2252,560Item11.190.021.1952,560-----------63,397.51-----------
9Customer1PR116-Jan-2210Item999.000.1099.0010-----------991.00-----------
10Customer1PR119-Jan-2211Item789.000.2089.0011-----------981.20-----------
11Customer1PR122-Jan-2212Item479.000.3079.0012-----------951.60-----------
12Customer1PR119-Feb-2217Item655.000.3055.00-17-----------940.10----------
13Customer1PR119-Mar-2213Item879.000.3079.00--13-----------1,030.90---------
Plan
Cell Formulas
RangeFormula
I5:T13I5=(EOMONTH($C5,0)=EOMONTH(I$4,0))*$D5
AG5:AR13AG5=I5*($F5+$G5)

Then on the Overview sheet, the same comment applies with regard to the month headings, because these headings need to be matched to those on the Plan sheet. I could find no where on the Overview sheet where one would know that this summary applies to Customer1...other than looking into the formulas to find them hardcoded for "customer1". This isn't very efficient, as it would require revising the formulas for a different customer. I designated cell A3 for entering the customer name, which is then used in a FILTER function to determine which items apply to that customer during the month indicated by the column heading. Dynamic array formulas are used to extract the red/orange totals from the Plan sheet, as well as the item/material percentages from the List sheet, and then a SUMPRODUCT is performed to arrive at the breakdown of the total, split by specified percentages among the various Materials listed in the Overview sheet. To make the formula a little easier to follow, I grouped various definitions for cell references/ranges and formulas inside a LET function. You will want to pay close attention to the first few lines where you'll find the following. Adjust them as necessary to fit your actual tables. Any change to the formula is made just once to the upper leftmost formula and then dragged throughout the table.
Excel Formula: 
cust,           $A$3,              user specified customer name
ovwmats,        $B$6:$B$18,        materials list on Overview sheet
plancust,       Plan!$A$5:$A$15,   customers column on Plan sheet
planpsd,        Plan!$C$5:$C$15,   PSD (date) column on Plan sheet
plandata,       Plan!$AG$5:$AR$15, main body of data in red/orange section of Plan sheet
planitems,      Plan!$E$5:$E$15,   item (SKU) column on Plan sheet
plandatecolhds, Plan!$AG$4:$AR$4,  horizontal range holding month headings in red/orance section of Plan sheet
listdata,       List!$B$2:$M$15,   main body of data on List lookup sheet
listmats,       List!$A$2:$A$15,   list of materials in first column of List lookup sheet
listdatacolhds, List!$B$1:$M$1,    horizontal range holding Item headings in List lookup sheet
MrExcel_20220511a.xlsx
ABCDE
1Calculation section
2
3Customer1Units
4Jan-22Feb-22Mar-22
5Order 11118652.49937.821030.90
6Material1791311.39660.42481.87
7Material2106428.0384.35341.89
8Material30.000.000.00
9Material4120290.13105.5373.23
10Material50.830.000.99
11Material655020.8653.2844.43
12Material70.000.0088.49
13Material845601.2434.240.00
14Material90.000.000.00
150.000.000.00
160.000.000.00
170.000.000.00
180.000.000.00
Overview
Cell Formulas
RangeFormula
C5:E5C5=SUM(C6:C18)
C6:E18C6=LET( cust, $A$3, ovwmats, $B$6:$B$18, plancust, Plan!$A$5:$A$15, planpsd, Plan!$C$5:$C$15, plandata, Plan!$AG$5:$AR$15, planitems, Plan!$E$5:$E$15, plandatecolhds, Plan!$AG$4:$AR$4, listdata, List!$B$2:$M$15, listmats, List!$A$2:$A$15, listdatacolhds, List!$B$1:$M$1, cm, (plancust=cust)*(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,""))
 
Upvote 0
KRice said:
I have a few observations and suggestions to make your task easier. Change the month column headings on the Plan sheet to be actual dates and then use a format option to display month names. Here, instead of entering "Jan" for the 1st month, I entered 1/1/2022 and formatted the display to show MMM-YY. By doing this, you can use references to the month headings in formulas, which means you don't have to hardcode the dates in your formulas. This simplifies the Plan sheet so that you have only two formulas that automatically adjust as the formula is copied throughout the table body.
MrExcel_20220511a.xlsx
ABCDEFGHIJKLMNOPQRSTAGAHAIAJAKALAMANAOAPAQAR
32022UnitsTotals
4CustomerProject CodePSDQTYSKUReg. Material /SKUPrem. Material/SKUApplique /SKUJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22
5Customer1PR114-Jan-22925,000Item11.190.021.19925,000-----------1,115,728.69-----------
6Customer2PR121-Jan-22315,408Item21.840.420.26315,408-----------713,043.42-----------
7Customer3PR128-Jan-22378,900Item30.350.020.01378,900-----------137,035.50-----------
8Customer4PR221-Jan-2252,560Item11.190.021.1952,560-----------63,397.51-----------
9Customer1PR116-Jan-2210Item999.000.1099.0010-----------991.00-----------
10Customer1PR119-Jan-2211Item789.000.2089.0011-----------981.20-----------
11Customer1PR122-Jan-2212Item479.000.3079.0012-----------951.60-----------
12Customer1PR119-Feb-2217Item655.000.3055.00-17-----------940.10----------
13Customer1PR119-Mar-2213Item879.000.3079.00--13-----------1,030.90---------
Plan
Cell Formulas
RangeFormula
I5:T13I5=(EOMONTH($C5,0)=EOMONTH(I$4,0))*$D5
AG5:AR13AG5=I5*($F5+$G5)

Then on the Overview sheet, the same comment applies with regard to the month headings, because these headings need to be matched to those on the Plan sheet. I could find no where on the Overview sheet where one would know that this summary applies to Customer1...other than looking into the formulas to find them hardcoded for "customer1". This isn't very efficient, as it would require revising the formulas for a different customer. I designated cell A3 for entering the customer name, which is then used in a FILTER function to determine which items apply to that customer during the month indicated by the column heading. Dynamic array formulas are used to extract the red/orange totals from the Plan sheet, as well as the item/material percentages from the List sheet, and then a SUMPRODUCT is performed to arrive at the breakdown of the total, split by specified percentages among the various Materials listed in the Overview sheet. To make the formula a little easier to follow, I grouped various definitions for cell references/ranges and formulas inside a LET function. You will want to pay close attention to the first few lines where you'll find the following. Adjust them as necessary to fit your actual tables. Any change to the formula is made just once to the upper leftmost formula and then dragged throughout the table.
Excel Formula: 
cust,           $A$3,              user specified customer name
ovwmats,        $B$6:$B$18,        materials list on Overview sheet
plancust,       Plan!$A$5:$A$15,   customers column on Plan sheet
planpsd,        Plan!$C$5:$C$15,   PSD (date) column on Plan sheet
plandata,       Plan!$AG$5:$AR$15, main body of data in red/orange section of Plan sheet
planitems,      Plan!$E$5:$E$15,   item (SKU) column on Plan sheet
plandatecolhds, Plan!$AG$4:$AR$4,  horizontal range holding month headings in red/orance section of Plan sheet
listdata,       List!$B$2:$M$15,   main body of data on List lookup sheet
listmats,       List!$A$2:$A$15,   list of materials in first column of List lookup sheet
listdatacolhds, List!$B$1:$M$1,    horizontal range holding Item headings in List lookup sheet
MrExcel_20220511a.xlsx
ABCDE
1Calculation section
2
3Customer1Units
4Jan-22Feb-22Mar-22
5Order 11118652.49937.821030.90
6Material1791311.39660.42481.87
7Material2106428.0384.35341.89
8Material30.000.000.00
9Material4120290.13105.5373.23
10Material50.830.000.99
11Material655020.8653.2844.43
12Material70.000.0088.49
13Material845601.2434.240.00
14Material90.000.000.00
150.000.000.00
160.000.000.00
170.000.000.00
180.000.000.00
Overview
Cell Formulas
RangeFormula
C5:E5C5=SUM(C6:C18)
C6:E18C6=LET( cust, $A$3, ovwmats, $B$6:$B$18, plancust, Plan!$A$5:$A$15, planpsd, Plan!$C$5:$C$15, plandata, Plan!$AG$5:$AR$15, planitems, Plan!$E$5:$E$15, plandatecolhds, Plan!$AG$4:$AR$4, listdata, List!$B$2:$M$15, listmats, List!$A$2:$A$15, listdatacolhds, List!$B$1:$M$1, cm, (plancust=cust)*(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,""))
Click to expand...

Apologies if I was unclear, the Overview is not summarised by customer but by the material needed. The reason to have the Plan specify the Customer/SKU is to determine the formula needed to multiply the material need by.
 
Upvote 0
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
Click to expand...
That point was clear, and the Overview is broken out by Material...but the material breakouts depend on the Items...and the items to consider depend on the customer and the month. With your post #1 formulas, you have no convenient way to run an overview for Customer2. The formulas would need to be edited (changing Customer1 to Customer2 everywhere). My suggestion avoids this, as you have to change only one cell (A3) to run an overview for a different customer. That cell could be tied to a Data Validation list formed from the entire list of customers so that you would simply use a drop down list to select one of them. Try changing A3 to Customer2 to see how this works, as that will look back at the Plan sheet and find that Customer2 is associated only with Item2 in January...and the Overview summary adjusts accordingly to show the materials breakdown for Item2 and the January 2022 total (from the red/orange section of Plan) is apportioned by those material percentages.

If you are not familiar with the XL2BB add-in used here, you can click on the clipboard icon in the upper left of a posted working example to copy the mini-sheet to your clipboard, and then go to a fresh worksheet in your workbook, navigate to the same cell as shown in the upper left of the posted example, and paste. The content of the mini-sheet, including formulas, is inserted.
 
Last edited:
Upvote 0
And if you don't care about filtering by a specific customer, a small change to the LET function to eliminate the customer filter gives an overview for all customers for each month:
MrExcel_20220511a.xlsx
ABCDE
1Calculation section
2
3All CustomersUnits
4Jan-22Feb-22Mar-22
5Order 12032128.93937.821030.90
6Material11374085.77660.42481.87
7Material2164486.6884.35341.89
8Material30.000.000.00
9Material4245035.72105.5373.23
10Material50.830.000.99
11Material685189.4253.2844.43
12Material767807.860.0088.49
13Material895522.6434.240.00
14Material90.000.000.00
Overview_AllCustomers
Cell Formulas
RangeFormula
C5:E5C5=SUM(C6:C18)
C6:E14C6=LET( ovwmats, $B$6:$B$18, plancust, Plan!$A$5:$A$15, planpsd, Plan!$C$5:$C$15, plandata, Plan!$AG$5:$AR$15, planitems, Plan!$E$5:$E$15, plandatecolhds, Plan!$AG$4:$AR$4, listdata, List!$B$2:$M$15, listmats, List!$A$2:$A$15, listdatacolhds, List!$B$1:$M$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,""))
 
Upvote 0
KRice said:
That point was clear, and the Overview is broken out by Material...but the material breakouts depend on the Items...and the items to consider depend on the customer and the month. With your post #1 formulas, you have no convenient way to run an overview for Customer2. The formulas would need to be edited (changing Customer1 to Customer2 everywhere). My suggestion avoids this, as you have to change only one cell (A3) to run an overview for a different customer. That cell could be tied to a Data Validation list formed from the entire list of customers so that you would simply use a drop down list to select one of them. Try changing A3 to Customer2 to see how this works, as that will look back at the Plan sheet and find that Customer2 is associated only with Item2 in January...and the Overview summary adjusts accordingly to show the materials breakdown for Item2 and the January 2022 total (from the red/orange section of Plan) is apportioned by those material percentages.

If you are not familiar with the XL2BB add-in used here, you can click on the clipboard icon in the upper left of a posted working example to copy the mini-sheet to your clipboard, and then go to a fresh worksheet in your workbook, navigate to the same cell as shown in the upper left of the posted example, and paste. The content of the mini-sheet, including formulas, is inserted.
Click to expand...
Yes, however we are not wanting the view to be customer dependent as it does not matter to the overall order scheme. I was hoping for a way to consolidate those amounts across all plan orders via material need without having to have hardcoded formulas for each.

A thought was to have the material list be a single range with columns for the SKUS instead of customer/sku based
 
Upvote 0
Did you see my post #5? Your initial post mentions "filtered by customer" and your formulas included requirements to match "customer1". But if you do not care about the customer filtering, then post #5 ignores them....the overview is for each month, all items ordered by all customers during that month. Then those items are used to extract the relevant material percentages from the List sheet to aggregate the breakouts by material. These formulas have no hardcoding for customer, date/month, item, or material. I believe this is exactly what you've just described, is it not?
 
Upvote 0
This version offers more flexibility, combining the ideas behind the offerings in posts #2 and 5. This creates a unique list of customers (to be hidden out of the way somewhere) and this list is used in the customer selection dropdown in cell A3. If you want to see a specific customer, you can choose them here. If you want to see the overview for all customers, choose "All" from the list. Here I've chosen "Customer2" to make examination of the results easier to follow. The main formula finds only one entry for Customer2 (in January for only one item...Item2 for a total of 713043.42). That 713042.42 is apportioned by the material percentages that apply to Item2 (taken from the List sheet).
MrExcel_20220511a.xlsx
ABCDEFADAE
2Select Customer or "All"
3Customer2Units
4Jan-22Feb-22Mar-22
5Order 1713,043.42--
6Material1526799.96  All
7Material217140.50  Customer1
8Material30.00  Customer2
9Material454577.34  Customer3
10Material50.00  Customer4
11Material627049.01  
12Material767807.86  
13Material819668.75  
14Material90.00  
Overview
Cell Formulas
RangeFormula
C5:E5C5=SUM(C6:C18)
C6:E14C6=LET( cust, $A$3, ovwmats, $B$6:$B$18, plancust, Plan!$A$5:$A$15, planpsd, Plan!$C$5:$C$15, plandata, Plan!$AG$5:$AR$15, planitems, Plan!$E$5:$E$15, plandatecolhds, Plan!$AG$4:$AR$4, listdata, List!$B$2:$M$15, listmats, List!$A$2:$A$15, listdatacolhds, List!$B$1:$M$1, cm, IF(cust="All",1,plancust=cust)*(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,""))
AE6:AE10AE6=LET(plancust, Plan!$A$5:$A$15, ucust, SORT(UNIQUE(FILTER(plancust,plancust<>""))), numcust, COUNTA(ucust), rowindex, SEQUENCE(numcust+1), custselections, IF(rowindex=1, "All", IF(rowindex<=numcust+1, INDEX(ucust,rowindex-1),"")), custselections)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A3List=$AE$6#
 
Upvote 0
I can't verify the solution works because using all customer option you've supplied (#5), nothing pulls in. I made all the changes suggested and confirmed that the references were as intended. I made a minor modification to the list so it was a single range for all skus with intelligent modifiers for customer instead of different ranges for customer/sku
List
Example (version 1).xlsb
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1C1Item1C1Item2C1Item3C1Item4C1Item5C1Item6C1Item7C1Item8C1Item9C1Item10C1Item11C1Item12C2Item1C2Item2C2Item3C2Item4C2Item5C2Item6C2Item7C2Item8C2Item9C2Item10C2Item11C2Item12C2Item13
2Material170.8%73.9%8.1%2.4%0.0%70.3%73.9%46.7%81.2%96.8%36.1%69.4%
3Material29.4%2.4%25.5%96.9%86.7%9.0%8.3%33.2%10.5%0.0%18.1%8.9%
4Material3
5Material410.8%7.7%46.2%0.0%1.8%11.2%12.2%7.1%2.4%3.2%13.9%15.7%
6Material50.0%0.0%0.0%0.0%0.0%0.0%0.0%0.1%0.0%0.0%6.9%0.4%
7Material64.9%3.8%0.0%0.7%10.6%5.7%5.6%4.3%5.9%0.0%25.0%5.2%
8Material70.0%9.5%0.0%0.0%0.0%0.0%0.0%8.6%0.0%0.0%0.0%0.0%
9Material84.1%2.8%20.2%0.0%0.0%3.6%0.0%0.0%0.0%0.0%0.0%0.4%
10Material90.0%0.0%0.0%0.0%0.9%0.0%0.0%0.0%0.0%0.0%0.0%
11Material10
12Material11
13Material12
14Material1397.5%97.5%98.5%98.5%98.5%98.5%97.5%98.9%98.5%100.0%100.0%100.0%
15Material142.5%2.5%1.5%1.5%1.5%1.5%2.5%0.8%2.5%
List

Plan
Example (version 1).xlsb
ABCDEIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
12022UnitsTotal Sheets (Premium + Regular)Total Sheets (Premium + Regular)
2CustomerProject CodePSD QTY SKUJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22
3C1PR11/14/2022925,000C1Item1925000000000000001096387.784000000000001115728.69300000000000
4C1PR11/21/2022315,408C1Item23154080000000000081064.1688500000000000713043.418900000000000
5C1PR11/28/2022378,900C1Item3378900000000000005446.687500000000000137035.500000000000
6C1PR21/21/202252,560C1Item1525600000000000062298.531820000000000063397.5136400000000000
7C1PR11/16/202252,560C1Item95256000000000000520344000000000000520869600000000000
8C1PR11/19/2022990,000C1Item79900000000000000088110000000000000008830800000000000000
9C1PR11/22/2022990,000C1Item49900000000000000078210000000000000007850700000000000000
Plan
Cell Formulas
RangeFormula
I3:T9I3=(EOMONTH($C3,0)=EOMONTH(I$2,0))*$D3
U3:AF9U3=I3*+$H3
AG3:AR9AG3=I3*($F3+$G3)

Overview
Example (version 1).xlsb
ABCDE
1Calculation section
2
3All CustomersUnits
41/1/20222/1/20223/1/2022
5Order 1000
6Material1   
7Material2   
8Material3   
9Material4   
10Material5   
11Material6   
OverviewTst
Cell Formulas
RangeFormula
C5:E5C5=SUM(C6:C18)
C6:E11C6=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:$M$1, cm, (plancust=cust)*(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,""))
 
Upvote 0
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:
Excel Formula: 
=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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
I could use help with a bit of a complex table addressing problem
Replies
1
Views
192
JenniferMurphy
JenniferMurphy
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
237
Joe4
Joe4
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
233
arun_eagertolearn
A
J
  • Solved
returning a value if multiple criteria are true
Replies
3
Views
385
etaf
etaf
J
  • Solved
conditional formatting with multiple criteria
Replies
8
Views
382
jbrown021286
J

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top