Sum Material Based on Different Criteria

nathanthomson11

New Member
Joined
Apr 4, 2019
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Unfortunately the office restricts my ability to upload a file so I'll do my best to explain what I'm looking for based on the attached image.

What I am trying to do is sum in table #3 based on information from Table #1 & Table #2. For example, in C13, I want to sum all of "Ford" material in Table #2 under the matching Calendar week - in this case "202101". However, in order to know what material in Table #2 is "Ford", it also needs to reference Table #1 to see what material number belongs to "Ford". The total in C13 should be 6,000 - the sum of "10001" & "10005" under calendar week "202101".

I suspect it's either a SUMPRODUCT or INDEXMATCH but I can't find a proper combination.
 

Attachments

  • Capture_003.JPG
    Capture_003.JPG
    76.5 KB · Views: 15

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
.. or another option (assuming the previous results are correct :))

nathanthomson11.xlsm
BCDEFGHIJK
1
2ProgramMaterial #Material #202101202102202103202104202105202106
3Ford1000198765100015001000120014001600
4VW1000810005500055005100600070004000
5GMC1000310003300033003000350042004300
6GMC1000422222150017001500150020002100
7VW1000610006270028002700270032003300
8Ford1000510008130013001500110016002000
9VW1000710007600061006000600060006000
10
11
12Program202101202104202103202102202105202106
13Ford500060005100550070004000
14GMC300035003000330042004300
15VW10000980010200102001080011300
Test6
Cell Formulas
RangeFormula
C13:H15C13=SUMPRODUCT($F$3:$K$9*($F$2:$K$2=C$12)*(XLOOKUP($E$3:$E$9,$C$3:$C$9,$B$3:$B$9,"")=$B13))
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

nathanthomson11

New Member
Joined
Apr 4, 2019
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Let's try a change of approach then.

nathanthomson11.xlsm
BCDEFGHIJK
1
2ProgramMaterial #Material #202101202102202103202104202105202106
3Ford1000198765100015001000120014001600
4VW1000810005500055005100600070004000
5GMC1000310003300033003000350042004300
6GMC1000422222150017001500150020002100
7VW1000610006270028002700270032003300
8Ford1000510008130013001500110016002000
9VW1000710007600061006000600060006000
10
11
12Program202101202104202103202102202105202106
13Ford500060005100550070004000
14GMC300035003000330042004300
15VW10000980010200102001080011300
Test5
Cell Formulas
RangeFormula
C13:H15C13=SUM(FILTER(FILTER($F$3:$K$9,$F$2:$K$2=C$12),ISNUMBER(MATCH($E$3:$E$9,FILTER($C$3:$C$9,$B$3:$B$9=$B13),0))))
Thank you so much!!
 

nathanthomson11

New Member
Joined
Apr 4, 2019
Messages
19
Office Version
  1. 365
Platform
  1. Windows
@Peter_SSs - I'm hoping you can help me again. I have a very similar situation as the one you did for me above however slightly different.

What I am trying to do is sum in table #3 based on information from Table #1 & Table #2. For example, in C13 I want to sum the quantity of "Ford" product being build in that particular week which is displayed in Table 2 but shown in specific dates, not weeks. However, in order to know what material in Table #2 is "Ford", it also needs to reference Table #1 to see what material number belongs to "Ford". The total in C13 should be 1,000 - The Ford material is 10001 & 10005 but 10001 is built in cw18 (202118) and 10005 is built in cw19 (202119).

Apologies again that I am only able to provide a screenshot..
 

Attachments

  • Capture_001.PNG
    Capture_001.PNG
    13.3 KB · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,130,124
Messages
5,640,249
Members
417,131
Latest member
Seanr19871

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
Top