Pivot or something else

[ExcelKid_10]

Hi all-

I'm trying to create a flexible summary of the following data:

What I'd like to be able to do is allow the user to select a market name(s) and be able to calculate a price per unit (by month and type code) for the combined markets. For example, if a user selected North Dakota and then Wisconsin the resulting summary would add up the report quantity and dollar amounts and then calculate a price per unit for the combined market by each month. I've tried a pivot however I have been unable to get a grouping by market name. Does anybody have any thoughts on a good way to go about this? Perhaps a VBA route is more appropriate. Thanks for any replies!

 

[ExcelKid_10]

Book1.xls
	A	B	C	D	E	F	G
1	Month	Year	Type Code	Market Name	Report Quantity	Report Dollar Amt	Price Per Unit
2	1	2004	N	North Dakota	2,500	287,221	$114.89
3	2	2004	N	North Dakota	852	287,221	$337.11
4	3	2004	N	North Dakota	733	517,705	$706.28
5	4	2004	N	North Dakota	202	582,899	$2,885.64
6	5	2004	N	North Dakota	350	582,899	$1,665.43
7	6	2004	N	North Dakota	507	703,154	$1,386.89
8	7	2004	N	North Dakota	150	751,450	$5,009.67
9	8	2004	N	North Dakota	750	774,206	$1,032.27
10	9	2004	N	North Dakota	110	812,036	$7,382.14
11	10	2004	N	North Dakota	850	812,036	$955.34
12	11	2004	N	North Dakota	900	812,036	$902.26
13	12	2004	N	North Dakota	455	812,036	$1,784.69
14	1	2004	R	North Dakota	2,000	687,952	$343.98
15	2	2004	R	North Dakota	550	1,033,829	$1,879.69
16	3	2004	R	North Dakota	450	1,035,160	$2,300.35
17	4	2004	R	North Dakota	653	1,093,599	$1,674.73
18	5	2004	R	North Dakota	698	1,391,859	$1,994.07
19	6	2004	R	North Dakota	879	1,391,859	$1,583.46
20	7	2004	R	North Dakota	546	1,391,859	$2,549.19
21	8	2004	R	North Dakota	359	1,417,347	$3,948.04
22	9	2004	R	North Dakota	872	1,471,723	$1,687.76
23	10	2004	R	North Dakota	489	1,536,988	$3,143.12
24	11	2004	R	North Dakota	444	1,572,872	$3,542.50
25	12	2004	R	North Dakota	698	1,608,278	$2,304.12
Sheet3