SUMIFS values per item

[Arek92]

Hi all,

I've been digging to find a solution to my problem but didn't seem to find any, so here I am.
I'm trying to write a formula that would sum values in a table column per item in the other column. The problem is, I need to sum values per item if that sum per item is greater than a specified amount, which is why I'm unable to name the item to sum the values by. Also, I may need to return that item in another table. Any help would be greatly appreciated.

 

[maabadi]

Welcome to MrExcel Message Board.
Try this:

TEST.xlsx
	A	B	C	D	E	F	G	H	I
1	Item #	Values			Sumif > 50		50
2	Item1	34		item1	73
3	Item2	28		item2	152
4	Item3	94		item3	94
5	Item4	81		item4	239
6	Item1	40
7	Item2	95
8	Item3	46
9	Item4	26
10	Item1	49
11	Item2	57
12	Item3	33
13	Item4	81
14	Item1	73
15	Item2	30
16	Item3	42
17	Item4	40
18	Item1	50
19	Item2	31
20	Item3	24
21	Item4	77
22
Sheet2

Cell Formulas
Range	Formula
E2:E5	E2	=SUMIFS($B$2:$B$21,$A$2:$A$21,D2,$B$2:$B$21,">"&$G$1)

 

[Arek92]

Welcome to MrExcel Message Board.
Try this:

TEST.xlsx
	A	B	C	D	E	F	G	H	I
1	Item #	Values			Sumif > 50		50
2	Item1	34		item1	73
3	Item2	28		item2	152
4	Item3	94		item3	94
5	Item4	81		item4	239
6	Item1	40
7	Item2	95
8	Item3	46
9	Item4	26
10	Item1	49
11	Item2	57
12	Item3	33
13	Item4	81
14	Item1	73
15	Item2	30
16	Item3	42
17	Item4	40
18	Item1	50
19	Item2	31
20	Item3	24
21	Item4	77
22
Sheet2

Cell Formulas
Range	Formula
E2:E5	E2	=SUMIFS($B$2:$B$21,$A$2:$A$21,D2,$B$2:$B$21,">"&$G$1)

Thank you for replying so quickly. My problem is a bit more complex to solve it as you proposed, for a couple of reasons:
1. There is more than one column to have the summed values match by, ie sum values per item per location. Several different items may be located in one location, as well as one item may be located in several different locations. Also, every item may have several different lot numbers.
2. I'm working on a large table (11k records) and listing all items seems counterproductive.
I figured out the first part and are able to return the location per item, but it still doesn't account for the lot number. What I'm looking for is the summed value in one location and of one lot number that matches the greater than criteria.
I apologize for not being concise enough in my opening post.

 

[maabadi]

Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

 

[Arek92]

Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

Zeszyt1.xlsx

1drv.ms

Here it is. I explained the problem in comments inside the file. I really appreciate your time and help.

 

[maabadi]

What means sum of Lot No. or Bin? They aren't Numbers.
Are You want Countifs?
Please Describe with Example.

 

[Arek92]

What means sum of Lot No. or Bin? They aren't Numbers.
Are You want Countifs?
Please Describe with Example.

Those stand for the lot no. and bin of the values the sum of which meets the greater than or equal to the required quantity criteria. For this to work, the formula would have to sum values of the same lot no. first, and the same bin second, also considering there may be other bins/lot no. meeting this criteria, so the minimal one would be desirable here. Let me try and visualize an example below:

Zeszyt2.xlsx

1drv.ms

I hope this provides more clarification on the problem.

 

[maabadi]

See Two formula at Columns J & K

Zeszyt1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item	Required qty	Single matchBin	Single match Lot no.	Single match Bin qty	Summed match bin	Summed match lot no.	Summed match qty		Sumproduct	Sumifs
2	101	100	#N/A	#N/A	0	1/1/2	1A	330		120	0
3	102	20	1/3/2	2A	20	1/2/1	2A	145		70	70
4	103	125	#N/A	#N/A	0	2/1/4	3A	435		170	0
5	104	35	3/2/3	4A	35	3/2/1	4A	75		40	40
6	105	100	#N/A	#N/A	0	2/5/1	5A	230		50	0
7	106	10	4/4/4	6B	15	4/4/4	6B	15		15	15
8
Master list

Cell Formulas
Range	Formula
C2:C7	C2	=INDEX(Bins,MATCH(1,([@[Single match Bin qty]]=Bins[Qty])*([@Item]=Bins[Item]),0),3)
D2:D7	D2	=INDEX(Bins,MATCH(1,([@[Single match Bin qty]]=Bins[Qty])*([@Item]=Bins[Item]),0),2)
E2:E7	E2	=MIN(IF([@Item]=Bins[Item],IF([@[Required qty]]<=Bins[Qty],Bins[Qty])))
F2:F7	F2	=INDEX(Bins,MATCH(1,([@[Required qty]]<=SUM(Bins[Qty]))*([@Item]=Bins[Item]),0),3)
G2:G7	G2	=INDEX(Bins,MATCH(1,([@[Required qty]]<=SUM(Bins[Qty]))*([@Item]=Bins[Item]),0),2)
H2:H7	H2	=SUM(IF([@Item]=Bins[Item],IF([@[Required qty]]<=SUM(Bins[Qty]),Bins[Qty])))
J2:J7	J2	=SUMPRODUCT((Bins[Qty])*(Bins[Qty]>=PickList[@[Single match Bin qty]])*(Bins[Bin]=PickList[@[Summed match bin]])*(PickList[@Item]=Bins[Item])*(Bins[Lot no.]=PickList[@[Summed match lot no.]]))
K2:K7	K2	=SUMIFS(Bins[Qty],Bins[Bin],PickList[@[Summed match bin]],Bins[Lot no.],PickList[@[Summed match lot no.]],Bins[Item],PickList[@Item],Bins[Qty],">="&PickList[@[Required qty]])
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Arek92]

See Two formula at Columns J & K

Zeszyt1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item	Required qty	Single matchBin	Single match Lot no.	Single match Bin qty	Summed match bin	Summed match lot no.	Summed match qty		Sumproduct	Sumifs
2	101	100	#N/A	#N/A	0	1/1/2	1A	330		120	0
3	102	20	1/3/2	2A	20	1/2/1	2A	145		70	70
4	103	125	#N/A	#N/A	0	2/1/4	3A	435		170	0
5	104	35	3/2/3	4A	35	3/2/1	4A	75		40	40
6	105	100	#N/A	#N/A	0	2/5/1	5A	230		50	0
7	106	10	4/4/4	6B	15	4/4/4	6B	15		15	15
8
Master list

Cell Formulas
Range	Formula
C2:C7	C2	=INDEX(Bins,MATCH(1,([@[Single match Bin qty]]=Bins[Qty])*([@Item]=Bins[Item]),0),3)
D2:D7	D2	=INDEX(Bins,MATCH(1,([@[Single match Bin qty]]=Bins[Qty])*([@Item]=Bins[Item]),0),2)
E2:E7	E2	=MIN(IF([@Item]=Bins[Item],IF([@[Required qty]]<=Bins[Qty],Bins[Qty])))
F2:F7	F2	=INDEX(Bins,MATCH(1,([@[Required qty]]<=SUM(Bins[Qty]))*([@Item]=Bins[Item]),0),3)
G2:G7	G2	=INDEX(Bins,MATCH(1,([@[Required qty]]<=SUM(Bins[Qty]))*([@Item]=Bins[Item]),0),2)
H2:H7	H2	=SUM(IF([@Item]=Bins[Item],IF([@[Required qty]]<=SUM(Bins[Qty]),Bins[Qty])))
J2:J7	J2	=SUMPRODUCT((Bins[Qty])*(Bins[Qty]>=PickList[@[Single match Bin qty]])*(Bins[Bin]=PickList[@[Summed match bin]])*(PickList[@Item]=Bins[Item])*(Bins[Lot no.]=PickList[@[Summed match lot no.]]))
K2:K7	K2	=SUMIFS(Bins[Qty],Bins[Bin],PickList[@[Summed match bin]],Bins[Lot no.],PickList[@[Summed match lot no.]],Bins[Item],PickList[@Item],Bins[Qty],">="&PickList[@[Required qty]])
Press CTRL+SHIFT+ENTER to enter array formulas.

Holy cow, thank you. The formula works like a charm, but... I can't understand how. Why does it reference the Single match Bin qty and Summed match bin columns from my table? These formulas that I wrote seem wrong and I don't understand how referencing them in your formula gave the exact result I was looking for. How would we go about only using the item no. and required qty from the "Master list" tab?

 

[maabadi]

You're Welcome & Glad We can Help. GO to Formula, And Check Step to Step TO understand it. (formula Tab > Evaluate formula)