Help with Index/match formulas

[willow1985]

I am needing help with 2 index match formulas (wondering if they are possible)

In the below picture I have my data source (left) and my table (right)
In column H and I on the right is where I am wanting the 2 index match formulas (in yellow).
In the table on the right, if you enter the Job # in column G, I would like the following to happen:

The first index match formula in column H: I want it to Sum all Qty Shipped for every Job# match
The second index match formula in column I (I don't know if it is possible): I would want it to return the results in order they are found. 1st result, 2nd result etc until no more matches.

Any help would be appreciated

 

[Fluff]

You can do col H with a sumif
=sumifs(b:b,a:a,g2)

Can you post some sample data using the XL2BB add-in, that way I don't need to recreate your data, for the other formula. Thanks

 

[gaz_chops]

H would be a sumif, so
=sumifs($B$2:$B$12,$A$2:$A$12,G2)

I try
=IFERROR(AGGREGATE(15,6,($C$2:$C$12)/($A$2:$A$12=$G2),ROW(A1)),"")

 

[CA_Punit]

Is this what you want

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	Job#	Qty Shipped	Packet List			Count		Total Quantity Shipped		Countif
3	JB1517	4	1234				JB1517	5	1234
4	JB1517	1	4444				JB1517	5	4444
5	JB1517	0	7929				JB1517	5	7929
6	JB1528	2	5578				JB1528	2	5578
7	JB1522	4	1182				JB1522	5	1182
8	JB1522	1	1209				JB1522	5	1209
9	JB1522	0	1828				JB1522	5	1828
10	JB1537	71	1187				JB1537	71	1187
11	JB1537	0					JB1537	71	0
12	JB1540	5	8245				JB1540	5	8245
13
14
15
Sheet3

Cell Formulas
Range	Formula
H3:H12	H3	=SUMIF($A$3:$A$12,G3,$B$3:$B$12)
I3:I12	I3	=IFERROR(INDEX($C$3:$C$12,AGGREGATE(15,6,ROW($A$1:$A$10)/($A$3:$A$12=G3),COUNTIF($G$3:G3,G3))),"")

 

[Fluff]

@CA_Punit
I would recommend using something like
=INDEX($C$2:$C$12,AGGREGATE(15,6,(ROW($C$2:$C$12)-ROW($C$2)+1)/($A$2:$A$12=G3),COUNTIFS(G$3:G3,G3)))

If you insert another row above the data, or delete row 1 then your formula will fail.

 

[Fluff]

Using the data that CA_Punit created

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	Job#	Qty Shipped	Packet List					Total Quantity Shipped	Packlist
2	JB1517	4	1234				JB1517	5	1234
3	JB1517	1	4444				JB1517	5	4444
4	JB1517	0	7929				JB1517	5	7929
5	JB1528	2	5578				JB1528	2	5578
6	JB1522	4	1182				JB1522	5	1182
7	JB1522	1	1209				JB1522	5	1209
8	JB1522	0	1828				JB1522	5	1828
9	JB1537	71	1187				JB1537	71	1187
10	JB1537	0					JB1537	71	0
11	JB1540	5	8245				JB1540	5	8245
Data

Cell Formulas
Range	Formula
H2:H11	H2	=SUMIFS(B:B,A:A,G2)
I2:I11	I2	=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/($A$2:$A$11=G2),COUNTIFS(G$2:G2,G2)))

 

[CA_Punit]

Thanks @Fluff

Will add to my knowledge. And please do help me if i make error.

 

[willow1985]

Using the data that CA_Punit created

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	Job#	Qty Shipped	Packet List					Total Quantity Shipped	Packlist
2	JB1517	4	1234				JB1517	5	1234
3	JB1517	1	4444				JB1517	5	4444
4	JB1517	0	7929				JB1517	5	7929
5	JB1528	2	5578				JB1528	2	5578
6	JB1522	4	1182				JB1522	5	1182
7	JB1522	1	1209				JB1522	5	1209
8	JB1522	0	1828				JB1522	5	1828
9	JB1537	71	1187				JB1537	71	1187
10	JB1537	0					JB1537	71	0
11	JB1540	5	8245				JB1540	5	8245
Data

Cell Formulas
Range	Formula
H2:H11	H2	=SUMIFS(B:B,A:A,G2)
I2:I11	I2	=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/($A$2:$A$11=G2),COUNTIFS(G$2:G2,G2)))

The Sumifs formula I should have known. having an off day...

I tried the other and got it to work with my original workbook.

Thank you very much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[willow1985]

I owe you about a million coffee's right about now Fluff, thank you again

And thank you CA_Punit and gaz_chops ? ?