Summing values in table

[Tabadi]

I have a table with the following headings:

Salesman Suburb Amount

There are ~3,000 entries with multiples for each salesman & each suburb.
The whole array is named "sales".

I want to create a summary table on another worksheet that will give the sum
of all sales for each salesman and each suburb:

Salesman Suburb 1 Suburb 2 Suburb 3 ...... etc
Salesman 1
Salesman 2
Salesman 3
|
etc

I need a formula that will sum the values in "sales" that meets the criteria
of Salesman name and suburb.

 

[Domenic]

Assuming that A2:A4 contains the salesman name, and B11 contains the
suburb, try...

B2, copied across and down:

=SUMPRODUCT(--(INDEX(Sales,0,1)=$A2),--(INDEX(Sales,0,2)=B$1),INDEX(Sales
,0,3))

Hope this helps!

http://www.xl-central.com

 

[EvilTony]

You need a pivot table

Select all your data, including column headings
Under the Data menu select Pivot Table and Pivot Chart Report

Drag Salesman to the ROW area
Drag Suburb to the COLUMN area
Drag Amount to the DATA area

 

[Roger Govier]

Hi

Use a Pivot Table.
Data>Pivot Table>Next>Source> =Sales>Finish
On the PT skeleton that appears on a new sheet
Drag Salesman from the Field list to the Row area
Drag Suburb to the Column area
Drag Amount to the Data area

Job done. Not a formula to type.