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:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table_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:

[TABLE="class: cms_table_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]ABC Inc[/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]ABC Inc[/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]ABC Inc[/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]ABC Inc[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_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!

 

[Chrisdontm]

It might be a good idea to use all the cells to the right to return each PO into different cells...

Other than that, would you happen to know the most PO's expected to be returned...?

 

[Peter_SSs]

I would try to avoid whole column references in your formulas. It doesn't apply to every function, but you shouldn't be leaving Excel to possibly evaluate over 1,000,000 rows if you are only using say a few hundred or a few thousand say.

You could try this udf. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function getPO(Data As Range, POCol As Long, _
               LocCol As Long, Loc As String, _
               GrdCol As Long, Grd As String, _
               CoCol As Long, Co As String) As String
  Dim a As Variant
  Dim i As Long
  Dim s As String

  a = Data.Value
  For i = 1 To UBound(a)
    If a(i, LocCol) = Loc Then
      If a(i, GrdCol) = Grd Then
        If a(i, CoCol) = Co Then
          s = s & ", " & a(i, POCol)
        End If
      End If
    End If
  Next i
  getPO = Mid(s, 3)
End Function

Excel Workbook
	A	B	C	D	E	F
1	Location	Grade	P.O.#	Company	Total P.O. (lbs)	Remaining on P.O. (lbs)
2	New York	275F	11111	SteelCore	50	20
3	New York	275F	22222	SteelCore	30	10
4	New York	180F	99999	ABC Inc	60	25
5	New York	275F	44444	ABC Inc	10	5
6	Chicago	180F	55555	SteelCore	50	15
7	Chicago	180F	66666	ABC Inc	40	15
8	Chicago	180F	88888	ABC Inc	20	10
9	Chicago	275F	33333	ABC Inc	15	5
WKST1

The arguments for the function are, in order:
The range containing the columns of interest from the 'WKST1' sheet. I've used A$1:D$1000. You need to ensure the $1000 is at least as big as any data you will have.
The column within the above range that houses the PO numbers to harvest (3)
The column within the above range that houses the Locations to check (1)
The 'INV2' cell housing the actual Location value of interest (A2 for the first formula)
The column within the above range that houses the Grade to check (2)
The 'INV2' cell housing the actual Grade value of interest (B2 for the first formula)
The column within the above range that houses the Company to check (4)
The 'INV2' cell housing the actual Company value of interest (C2 for the first formula)

Formula in E2 is copied down.

Excel Workbook
	A	B	C	D	E
1	Location	Grade	Company	Amt Grade Remaining (lbs)	P.O. #(s)
2	New York	180F	SteelCore	0
3	New York	180F	ABC Inc	25	99999
4	New York	275F	SteelCore	30	11111, 22222
5	New York	275F	ABC Inc	5	44444
6	Chicago	180F	SteelCore	15	55555
7	Chicago	180F	ABC Inc	25	66666, 88888
8	Chicago	275F	SteelCore	0
9	Chicago	275F	ABC Inc	5	33333
INV2

 

[Chrisdontm]

Peter,
That was a pretty slick answer...
I don't really get into VBA, but you sure make it look inviting...
Kudos!!!

 

[Peter_SSs]

Peter,
That was a pretty slick answer...
I don't really get into VBA, but you sure make it look inviting...
Kudos!!!

Thanks for your kind words.
Learning vba was one of the main reasons that I joined & began frequenting the forum.

 

[gloudy31]

Peter,

I apologize for the late reply. Thank you so much for your help! You are the man!

 

[Peter_SSs]

Peter,

I apologize for the late reply. Thank you so much for your help! You are the man!

You are very welcome. Thanks for the follow-up.