# 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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

##### Board Regular
Hi Dear,

Book1
BCDEFGHIJK
2ProgramMaterial #Material #202101202102202103202104202105202106
3Ford1000110001100015001000120014001600
4Ford1000510005500055005100600070004000
5GMC1000310003300033003000350042004300
6GMC1000410004150017001500150020002100
7VW1000610006270028002700270032003300
8VW1000810008130013001500110016002000
9VW1000710007600061006000600060006000
10
11
12Program202101202102202103202104202105202106
13Ford600070006100720084005600
14GMC450050004500500062006400
15VW10000102001020098001080011300
16TRUETRUETRUETRUETRUETRUE
Sheet3
Cell Formulas
RangeFormula
C13:H15C13=SUM(SUMIFS(F\$3:F\$9,\$E\$3:\$E\$9,\$C\$3:\$C\$9,\$B\$3:\$B\$9,\$B13))
C16:H16C16=SUM(F3:F9)=SUM(C13:C15)

Regards

#### Peter_SSs

##### MrExcel MVP, Moderator
That relies on the values in C3:C9 exactly matching the values in E3:E9. I suspect that may not be the case with the OP's actual data else you could just use this.

21 02 10.xlsm
BCDEFGHIJK
2ProgramMaterial #Material #202101202102202103202104202105202106
3Ford1000110001100015001000120014001600
4Ford1000510005500055005100600070004000
5GMC1000310003300033003000350042004300
6GMC1000410004150017001500150020002100
7VW1000610006270028002700270032003300
8VW1000810008130013001500110016002000
9VW1000710007600061006000600060006000
10
11
12Program202101202102202103202104202105202106
13Ford600070006100720084005600
14GMC450050004500500062006400
15VW10000102001020098001080011300
Test1
Cell Formulas
RangeFormula
C13:H15C13=SUMIF(\$B\$3:\$B\$9,\$B13,F\$3:F\$9)

If the order in table 1 may not be identical to table 2 (below I have swapped the two yellow rows) then try ..

21 02 10.xlsm
BCDEFGHIJK
2ProgramMaterial #Material #202101202102202103202104202105202106
3Ford1000110001100015001000120014001600
4VW1000810005500055005100600070004000
5GMC1000310003300033003000350042004300
6GMC1000410004150017001500150020002100
7VW1000610006270028002700270032003300
8Ford1000510008130013001500110016002000
9VW1000710007600061006000600060006000
10
11
12Program202101202102202103202104202105202106
13Ford600070006100720084005600
14GMC450050004500500062006400
15VW10000102001020098001080011300
Test2
Cell Formulas
RangeFormula
C13:H15C13=SUMPRODUCT(--(INDEX(\$B\$3:\$B\$9,MATCH(\$E\$3:\$E\$9,\$C\$3:\$C\$9,0))=\$B13),F\$3:F\$9)

Further, if the calendar weeks in table 2 do not exactly match the calendar weeks in table 3 (I have also swapped the green values) then you could try this

21 02 10.xlsm
BCDEFGHIJK
2ProgramMaterial #Material #202101202102202103202104202105202106
3Ford1000110001100015001000120014001600
4VW1000810005500055005100600070004000
5GMC1000310003300033003000350042004300
6GMC1000410004150017001500150020002100
7VW1000610006270028002700270032003300
8Ford1000510008130013001500110016002000
9VW1000710007600061006000600060006000
10
11
12Program202101202104202103202102202105202106
13Ford600072006100700084005600
14GMC450050004500500062006400
15VW10000980010200102001080011300
Test3
Cell Formulas
RangeFormula
C13:H15C13=SUMPRODUCT(--(INDEX(\$B\$3:\$B\$9,MATCH(\$E\$3:\$E\$9,\$C\$3:\$C\$9,0))=\$B13),INDEX(\$F\$3:\$K\$9,0,MATCH(C\$12,\$F\$2:\$K\$2,0)))

#### Marcelo Branco

##### MrExcel MVP
Peter,

Curiously this formula (2nd example) didn't work for me in Excel 2010.
=SUMPRODUCT(--(INDEX(\$B\$3:\$B\$9,MATCH(\$E\$3:\$E\$9,\$C\$3:\$C\$9,0))=\$B13),F\$3:F\$9)

I've gotten a #VALUE error

I have had to use a more complicated array formula.
=SUMPRODUCT(--(INDEX(\$B\$3:\$B\$9,N(IF(1,MATCH(\$E\$3:\$E\$9,\$C\$3:\$C\$9,0))))=\$B13),F\$3:F\$9)
confirmed with Ctrl+Shift+Enter

Regards

M.

#### Marcelo Branco

##### MrExcel MVP

Didn't work in Excel 2019 either (another machine i have)

Only the mentioned array formula above worked properly

M.

#### Peter_SSs

##### MrExcel MVP, Moderator
Hi Marcelo
Thanks for pointing those things out. I have difficulty remembering what works in what versions as I only have Excel 365 now.
Hopefully we will find out which one(s) work for the OP with Excel 2019.

#### nathanthomson11

##### New Member

Apologies for the lack of response guys - I had to table this project for a while but am back at it!

@Peter_SSs, the formula below seems to be working with one caveat - sometimes the two lists (B3:B9) & (E3:#9) don't have the exact same numbers which in this case , if one is missing, it gives me #N/A error.

Any suggestions to fix this?

RangeFormula
Cell Formulas
C13:H15C13=SUMPRODUCT(--(INDEX(\$B\$3:\$B\$9,MATCH(\$E\$3:\$E\$9,\$C\$3:\$C\$9,0))=\$B13),INDEX(\$F\$3:\$K\$9,0,MATCH(C\$12,\$F\$2:\$K\$2,0)))

#### Peter_SSs

##### MrExcel MVP, Moderator
sometimes the two lists (B3:B9) & (E3:#9) don't have the exact same numbers
Did you mean (C3:C9) & (E3:E9)?

Try

21 02 10.xlsm
BCDEFGHIJK
1
2ProgramMaterial #Material #202101202102202103202104202105202106
3Ford1000198765100015001000120014001600
4VW1000810005500055005100600070004000
5GMC1000310003300033003000350042004300
6GMC1000422222150017001500150020002100
7VW1000610006270028002700270032003300
8Ford1000510008130013001500110016002000
9VW1000710007600061006000600060006000
10
11
12Program202101202104202103202102202105202106
13Ford130011001500130016002000
14GMC300035003000330042004300
15VW137001470013800144001620013300
Test4
Cell Formulas
RangeFormula
C13:H15C13=SUMPRODUCT(\$F\$3:\$K\$9*(\$F\$2:\$K\$2=C\$12)*(ISNUMBER(MATCH(\$E\$3:\$E\$9,\$C\$3:\$C\$9,0)))*(\$B\$3:\$B\$9=\$B13))

#### nathanthomson11

##### New Member
Thank you for the quick response @Peter_SSs ! I think we are close however this formula doesn't seem to be calculating properly. For example, VW under 202101 should equal 10,000 (F7 + F8 + F9) but it is showing 13,700.

#### Peter_SSs

##### MrExcel MVP, Moderator
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))))

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

1,130,081
Messages
5,639,955
Members
417,120
Latest member
Pavithra devi

### 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