# Sum Material Based on Different Criteria

#### nathanthomson11

##### New Member
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
76.5 KB · Views: 15

#### Peter_SSs

##### MrExcel MVP, Moderator
.. 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
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
@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
13.3 KB · Views: 3

Replies
6
Views
47
Replies
2
Views
55
Replies
4
Views
504
Replies
5
Views
53
Replies
1
Views
63

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.

### Which adblocker are you using?

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

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