Excel report generation

[montassis]

Hi,

I'm having trouble wrapping my head around how to automatically generate a report from this data set.

The data set has Divisions, Areas, Agencies and the number of completed sales.

There are 4 divisions, North, South, East and West that have a number of Areas (geographic) and Agencies that can work across a number of different Divisions and Areas.

I need a report that will total the number of sales by Division and Area per Agency (see example report image below or attachment)

Here is an example of the raw data:

http://oi60.tinypic.com/1zo97rk.jpg

Here is an example of the report I want to generate automatically:

http://oi57.tinypic.com/k2llqu.jpg

Any ideas on how to go about this?

sample report.xlsx

[FDibbins]

Hi, welcome to the forum

Have you tried using a Pivot Table for this?

Set up as follows (for a suggestion)...
Column Labels - Division
Row labels - Area and Agency
Values - Completed

	A	B	C	D	E
11
12	Sum of Completed	Column Labels
13	Row Labels	North	South	West	Grand Total
14	Avondale			13	13
15	Carcity			10	10
16	Westside Cars			3	3
17	Church St		2		2
18	Joe's Mechanic		2		2
19	Riverside	8			8
20	Joe's Mechanic	3			3
21	Manic Motors	5			5
22	Smithdale		3		3
23	South Repairs		3		3
24	Springfield	5			5
25	Springfield Cars	5			5
26	Grand Total	13	5	13	31