This was most helpful and is how I have worked it all out. Thank you so much

"Max" wrote:

Use a pivot table, takes only seconds to set it up

Some easy steps to lead you in (xl2003)

Select a cell within the source table
Click Data Pivot table
Click Next Next

In step3 of the wiz., click Layout
Drag n drop Site in ROW area
Drag n drop Item No in ROW area (below Site)

Drag n drop Qty in DATA area
Drag n drop Value in DATA area (below Qty)
Both the above should appear as "Sum of"
Click OK Finish

Hop over to the pivot sheet (to the left)
In the pivot sheet,
Drag n drop "Data" over "Total",
and you should see the desired results magically appear
(like below):

Data
SITE ITEM NO Sum of QTY Sum of Value
DOOLEYS BRIS #1234 1 2
#1235 4 5.5
#1236 6 8
DOOLEYS BRIS Total 11 15.5
DOOLEYS SYD #1234 2 4
#1235 4 13.5
#1236 3 2
DOOLEYS SYD Total 9 19.5
ORIENT MELB #1234 2 7
#1235 2 3
#1236 6 5.5
ORIENT MELB Total 10 15.5
Grand Total 30 50.5

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"HayleyKingston" wrote:
I need to work out the Total qty, by item, by site (in column E). And the
total value, by item, by site (in column F).
See insert below.

I am unsure which formula to use or how to do this.

Can someone please help me?

Thankyou


A B C D E F
ITEM NO SITE QTY VALUE
#1234 DOOLEYS SYD 1 3.00
#1235 DOOLEYS SYD 2 6.50
#1236 DOOLEYS SYD 3 2.00
#1234 DOOLEYS SYD 1 1.00
#1235 DOOLEYS SYD 2 7.00
#1236 ORIENT MELB 3 1.00
#1234 ORIENT MELB 1 2.00
#1235 ORIENT MELB 2 3.00
#1236 ORIENT MELB 3 4.50
#1234 ORIENT MELB 1 5.00
#1235 DOOLEYS BRIS 2 2.50
#1236 DOOLEYS BRIS 3 1.00
#1234 DOOLEYS BRIS 1 2.00
#1235 DOOLEYS BRIS 2 3.00
#1236 DOOLEYS BRIS 3 7.00