conditional formula by using aggregate and count if........

[Umair Tariq]

below is my sheet
i need to put a formula to get my desired results,
In cell "F21" i put formula which is considering only cell "E32" but i want to select E column as a whole. furthermore this formula is not generating rows but using available rows, but i want to add new rows automatically by the use of formula

Arman Thread working.xlsx
	A	B	C	D	E	F
1			6
2	Jacket	Shoulder Joining x 2 (S.J), Sleeve Side Panel Attach x 2,Side Seam + P.O Label (F),
3	Jacket	Shoulder Top Stitch x 2 F/L (S.J),Sleeve Side Panel Top Stitch x 2,Arm Hole Top Stitch x 2 F/L (S.J)
4	Jacket	Bottom Kaaj x 4
5	Jacket	FOR REST OF OPERATIONS
6	Trouser	Back Yoke Joining (Safety),Back Rise Joining (Safety),Side Seam Joining (4 Joint) (O/L),Inseam Joining (5 Joint) (O/L)
7	Trouser	Back Yoke Top Stitch (FOA),Back Rise Top Stitch (FOA),Side Seam Top Stitch (FOA),Inseam Top Stitch (Regular FOA) Plain
8	Trouser	Belt grip Tape Attach,Waist band attach with main body (Belt Grip Tape Attached)
9	Trouser	Right Thigh Pocket Flap Top Stitch (4 Point),Left Thigh Mobile Pocket Flap Top Stitch (4 Point),Left Thigh Pocket Ballon Stand Hem DNLS,Back Pocket Flap Top Stitch (4 Point),Cross Pocket Attach Top Stitch x 2 (One Point Turn),Knee Pad Attach x 2 (Step 1),Ghazet Top Stitch,Back Pocket Attach (Step 2) DNLS x 2,Cordura Attach on Bottom (Back Panel),Right Thigh Pocket Flap Top stitch
10	Trouser	Eyelet Button Hole
11	Trouser	FOR REST OF OPERATIONS
12	Shorts	Back Yoke Joining (Safety),Back Rise Joining (Safety),Side Seam Joining (4 Joint) (O/L),Inseam Joining (5 Joint) (O/L)
13	Shorts	Back Yoke Top Stitch (FOA),Back Rise Top Stitch (FOA),Side Seam Top Stitch (FOA),Inseam Top Stitch (Regular FOA) Plain
14	Shorts	Belt grip Tape Attach,Waist band attach with main body (Belt Grip Tape Attached)
15	Shorts	Right Thigh Pocket Flap Top Stitch (4 Point),Left Thigh Mobile Pocket Flap Top Stitch (4 Point),Left Thigh Pocket Ballon Stand Hem DNLS,Back Pocket Flap Top Stitch (4 Point),Cross Pocket Attach Top Stitch x 2 (One Point Turn),Knee Pad Attach x 2 (Step 1),Ghazet Top Stitch,Back Pocket Attach (Step 2) DNLS x 2,Cordura Attach on Bottom (Back Panel),Right Thigh Pocket Flap Top stitch
16	Shorts	Eyelet Button Hole
17	Shorts	FOR REST OF OPERATIONS
18
19	Thread Usage Last One Year (Color Wise)
20
21	CUST	Article	Article Name	Color	Trouser	Back Yoke Joining (Safety),Back Rise Joining (Safety),Side Seam Joining (4 Joint) (O/L),Inseam Joining (5 Joint) (O/L)
22	ARS	ARS-NW6700	NORD WEAR FLEECE JACKET	BLACK		Back Yoke Top Stitch (FOA),Back Rise Top Stitch (FOA),Side Seam Top Stitch (FOA),Inseam Top Stitch (Regular FOA) Plain
23						Belt grip Tape Attach,Waist band attach with main body (Belt Grip Tape Attached)
24						Right Thigh Pocket Flap Top Stitch (4 Point),Left Thigh Mobile Pocket Flap Top Stitch (4 Point),Left Thigh Pocket Ballon Stand Hem DNLS,Back Pocket Flap Top Stitch (4 Point),Cross Pocket Attach Top Stitch x 2 (One Point Turn),Knee Pad Attach x 2 (Step 1),Ghazet Top Stitch,Back Pocket Attach (Step 2) DNLS x 2,Cordura Attach on Bottom (Back Panel),Right Thigh Pocket Flap Top stitch
25						Eyelet Button Hole
26						FOR REST OF OPERATIONS
27				NAVY BLUE	Trouser
28
29		ARS-NW6708	NORD WEAR FLEECE JACKET	BLACK
30				NAVY BLUE
31
Sheet5

Cell Formulas
Range	Formula
C1	C1	=COUNTIF($A$2:$A$17,$E$21)
F21:F31	F21	=IF(ROWS($C$1:C1)<=$C$1,INDEX($B$2:$B$17,AGGREGATE(15,3,($A$2:$A$17=$E$21)/($A$2:$A$17=$E$21)*(ROW($A$2:$A$17)-ROW($A$1)),ROWS($C$1:C1))),"")

 

[OaklandJim]

Unfortunately we have a language problem. I don't understand the objective so I maybe try again to describe what is needed. I will try to assist.