Filtered Sum

[tatorman]

This is a shared workbook, so I can't do VBA on this.

How do I Filter the unique values for this in place and show the sum of the materials.

QUEUE .xls
	A	B	C	D
1	OrderStart	Material	Orderqty
2	11/23/2004	BIKE	1
3	1/15/2005	MOTORCYCLE	6
4	2/27/2005	CAR	1
5	3/15/2005	TRUCK	3
6	1/3/2005	MOTORCYCLE	1
7	1/3/2005	CAR	8
8	1/11/2005	TRUCK	1
9	1/3/2005	BIKE	7
10	1/3/2005	BIKE	9
01.12.05

 

[tactps]

Your best bet is a pivot table. See the Excel help topics on this

 

[bigbob]

Hi
Would a combination of AUTOFILTER and SUBTOTAL do what you want You would have to do each category separately though
Bob

 

[Aladin Akyurek]

Using a formula system...

Keywords. Extracting a unique list or a list of distinct items. [ ISNUMBER, LOOKUP, MATCH, ROW, SUMIF ]

Book5
	A	B	C	D	E	F	G
1						4
2	OrderStart	Material	Orderqty	0		DistinctMaterial	TotalQty
3	11/23/2004	BIKE	1	1		BIKE	17
4	1/15/2005	MOTORCYCLE	6	2		MOTORCYCLE	7
5	2/27/2005	CAR	1	3		CAR	9
6	3/15/2005	TRUCK	3	4		TRUCK	4
7	1/3/2005	MOTORCYCLE	1
8	1/3/2005	CAR	8
9	1/11/2005	TRUCK	1
10	1/3/2005	BIKE	7
11	1/3/2005	BIKE	9
01.12.05

Formulas...

D2 must house a 0.

D3, copied down:

=IF(ISNUMBER(MATCH(B3,$B$2:B2,0)),"",LOOKUP(9.99999999999999E+307,$D$2:D2)+1)

F1:

=LOOKUP(9.99999999999999E+307,D:D)

F3, copied down:

=IF(ROW()-ROW(E$3)+1<=$F$1,LOOKUP(ROW()-ROW(E$3)+1,D:D,B:B),"")

G3, copied down:

=IF(F3<>"",SUMIF(B:B,F3,C:C),"")