In need of a greater Excel mind than my own! Referencing a Sumif Function??

[gloudy31]

Hi Helpers,

I'm not sure if this is indeed possible, but thought i'd give it a go as you all have been so helpful in the past. I have two tables in two separate worksheets ("WKST1" and "INV2") as shown below. INV2 references WKST1. In Column D of INV2 I am using a SUMIF equation to total all Remaining material that share the same location, grade, and company from Column F of WKST 1. So, for example, I used the equation =Sumif(WKST1!F:F,WKST1!D:D,"SteelCore",WKST1!A:A,'INV2'!A4,WKST1!B:B,'INV2'!B4) in Cell D4 of INV2 to gather the total amount of lbs of material in New York/275F Grade/SteelCore Company.

I would like to know if there is any equation(s) I can use in Column E of INV2 that will gather the P.O. #(s) from WKST1 that contribute to the sum of each Location/Grade/Company combination in INV2? For instance, P.O. #'s 11111 and 22222 from WKST1 make up the 30 lbs that are remaining in New York/Grade 275F/SteelCore Company (Cell D4 of INV2).

I realize this is likely not possible as some of the combinations in INV2 will return multiple P.O. #'s (like in the instance noted above), but was wondering if any of you out there with greater EXCEL minds than my own might have any ideas on alternative ways I could go about doing this???

At first glance i thought it could be accomplished through the use of a Pivot Table, however, that did not solve the problem.

I'd greatly appreciate any advice or feedback any of you could offer.

WKST1:

A B C D E F
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Location [/TD]
[TD]Grade[/TD]
[TD]P.O.# [/TD]
[TD]Company[/TD]
[TD="align: right"]Total P.O. (lbs)[/TD]
[TD="align: right"]Remaining on P.O. (lbs)[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]275F[/TD]
[TD]11111[/TD]
[TD]SteelCore[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]275F[/TD]
[TD]22222[/TD]
[TD]SteelCore[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]180F[/TD]
[TD]99999[/TD]
[TD]ABC Inc[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]275F[/TD]
[TD]44444[/TD]
[TD]ABC Inc[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]180F[/TD]
[TD]55555[/TD]
[TD]SteelCore[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]180F[/TD]
[TD]66666[/TD]
[TD]ABC Inc[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]180F[/TD]
[TD]88888[/TD]
[TD]ABC Inc[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]275F[/TD]
[TD]33333[/TD]
[TD]ABC Inc[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


INV2:

A B C D E
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Grade[/TD]
[TD]Company[/TD]
[TD]Amt Grade Remaining (lbs)[/TD]
[TD]P.O. #(s)[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]180F[/TD]
[TD]SteelCore[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]180F[/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]275F[/TD]
[TD]SteelCore[/TD]
[TD="align: right"]30**[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]275F[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]180F[/TD]
[TD]SteelCore[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]180F[/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]275F[/TD]
[TD]SteelCore[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]275F[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]















=Sumif(WKST1!F:F,WKST1!D:D,"SteelCore",WKST1!A:A,'INV2'!A4,WKST1!B:B,'INV2'!B4)**



Thanks!

 

[gloudy31]

Sorry! ABC Inc. should be entered into the empty 'Company' cells in INV2.