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))
 
Sort by date Sort by votes
KRice said:
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.
Click to expand...
Sure here is that portion
Example (version 1).xlsb
ABCDEFGHIJKLMNOPQRSTAGAHAIAJAKALAMANAOAPAQAR
12022UnitsTotal Sheets (Premium + Regular)
2CustomerProject CodePSD QTY SKUReg. 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
3C1PR11/14/2022925,000C1Item11.1852840910.0209090911.18528409925000000000000001115728.69300000000000
4C1PR11/21/2022315,408C1Item21.8396573270.4210444270.2570136731540800000000000713043.418900000000000
5C1PR11/28/2022378,900C1Item30.3450.0166666670.01437537890000000000000137035.500000000000
6C1PR21/21/202252,560C1Item11.1852840910.0209090911.18528409525600000000000063397.5136400000000000
7C1PR11/16/202252,560C1Item9990.1995256000000000000520869600000000000
8C1PR11/19/2022990,000C1Item7890.289990000000000000008830800000000000000
Plan
Cell Formulas
RangeFormula
I3:T8I3=(EOMONTH($C3,0)=EOMONTH(I$2,0))*$D3
AG3:AR8AG3=I3*($F3+$G3)
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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:
    Excel Formula: 
    --(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:
    Excel Formula: 
    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:
    Excel 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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
12022UnitsTotal Sheets (Premium + Regular)Total Sheets (Premium + Regular)
2CustomerProject CodePSD QTY SKUReg. Material /SKUPrem. Material/SKUApplique /SKUJanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSepOctNovDec
3C1PR11/14/2022925000C1Item11.18528410.02090911.1852841925000000000000001096387.784000000000001115728.69300000000000
4C1PR11/21/2022315408C1Item21.83965730.42104440.25701373154080000000000081064.1688500000000000713043.418900000000000
5C1PR11/28/2022378900C1Item30.3450.01666670.014375378900000000000005446.687500000000000137035.500000000000
6C1PR21/21/202252560C1Item11.18528410.02090911.1852841525600000000000062298.531820000000000063397.5136400000000000
7C1PR11/16/202252560C1Item9990.1995256000000000000520344000000000000520869600000000000
8C1PR11/19/2022990000C1Item7890.2899900000000000000088110000000000000008830800000000000000
9C1PR11/22/2022990000C1Item4790.3799900000000000000078210000000000000007850700000000000000
Plan
Cell Formulas
RangeFormula
I2:AR2I2=TRANSPOSE(EDATE(DATE($B$1,1,1),SEQUENCE(12,1,0)))
I3:T9I3=(EOMONTH($C3,0)=EOMONTH(I$2,0))*$D3
U3:AF9U3=I3*$H3
AG3:AR9AG3=I3*($F3+$G3)
Dynamic array formulas.

MrExcel_20220512_clemmer.xlsx
ABCDE
1Calculation section
2
3All CustomersUnits
4JanFebMar
5Order 1174,052,901.1300
6Material172714831.21  
7Material284092182.16  
8Material30.00  
9Material411140106.66  
10Material535249.77  
11Material65907200.82  
12Material767807.86  
13Material895522.64  
OverviewTst
Cell Formulas
RangeFormula
C4:N4C4=TRANSPOSE(EDATE(DATE(Plan!B1,1,1),SEQUENCE(12,1,0)))
C5:E5C5=SUM(C6:C18)
C6:E13C6=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.
 
Upvote 0
Solution
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...
Excel 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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Are these macros the best way to make custom sorts easily accessible?
Replies
2
Views
91
JenniferMurphy
JenniferMurphy
JenniferMurphy
  • Question Question
I could use help with a bit of a complex table addressing problem
Replies
1
Views
218
JenniferMurphy
JenniferMurphy
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
254
Joe4
Joe4
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
256
arun_eagertolearn
A
J
  • Solved
returning a value if multiple criteria are true
Replies
3
Views
394
etaf
etaf

Forum statistics

Threads
1,223,948
Messages
6,175,571
Members
452,652
Latest member
eduedu

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