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

Excel Facts

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

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Hi Dear,

Please check if the below satisfies your requirement:

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
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
Please check if the below satisfies your requirement:
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
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Adding to my previous post
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
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 4, 2019
Messages
19
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 4, 2019
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,557
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))))
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
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