Summing values in table
 

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.

Summing values in table
 

Assuming that A2:A4 contains the salesman name, and B1:D1 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

In article ,
Tabadi wrote:

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.

Summing values in table
 

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

--
It's a little fit bunny, this feeling inside


"Tabadi" wrote:

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.

Summing values in table
 

Hi

Use a Pivot Table.
DataPivot TableNextSource =SalesFinish
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.
--
Regards
Roger Govier

"Tabadi" wrote in message
...
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.