Hi,

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

Use a Pivot Table:

Data PivotTable

to produce some thing like:

Sum of Quantity
Item Site Total
#1234 DOOLEYS BRIS 1
DOOLEYS SYD 2
ORIENT MELB 2
#1234 Total 5
#1235 DOOLEYS BRIS 4
DOOLEYS SYD 4
ORIENT MELB 2
#1235 Total 10
#1236 DOOLEYS BRIS 6
DOOLEYS SYD 3
ORIENT MELB 6
#1236 Total 15
Grand Total 30


and

Sum of Value
Item Site Total
#1234 DOOLEYS BRIS 2
DOOLEYS SYD 4
ORIENT MELB 7
#1234 Total 13
#1235 DOOLEYS BRIS 5.5
DOOLEYS SYD 13.5
ORIENT MELB 3
#1235 Total 22
#1236 DOOLEYS BRIS 8
DOOLEYS SYD 2
ORIENT MELB 5.5
#1236 Total 15.5
Grand Total 50.5


See:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
Gary''s Student - gsnu200773


"HayleyKingston" wrote:

Hi,

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

I need to calculate the info in the far two columns because this is just an
extract and I need to analyse it further eg. rank top 10 used items by qty,
then same for value...

Is there a formula that will help just get these qtys and value to begin with?

"Gary''s Student" wrote:

Use a Pivot Table:

Data PivotTable

to produce some thing like:

Sum of Quantity
Item Site Total
#1234 DOOLEYS BRIS 1
DOOLEYS SYD 2
ORIENT MELB 2
#1234 Total 5
#1235 DOOLEYS BRIS 4
DOOLEYS SYD 4
ORIENT MELB 2
#1235 Total 10
#1236 DOOLEYS BRIS 6
DOOLEYS SYD 3
ORIENT MELB 6
#1236 Total 15
Grand Total 30


and

Sum of Value
Item Site Total
#1234 DOOLEYS BRIS 2
DOOLEYS SYD 4
ORIENT MELB 7
#1234 Total 13
#1235 DOOLEYS BRIS 5.5
DOOLEYS SYD 13.5
ORIENT MELB 3
#1235 Total 22
#1236 DOOLEYS BRIS 8
DOOLEYS SYD 2
ORIENT MELB 5.5
#1236 Total 15.5
Grand Total 50.5


See:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
Gary''s Student - gsnu200773


"HayleyKingston" wrote:

Hi,

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

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

As you seem averse to a Pivot table

=SUMPRODUCT(--A2:A20="#1234"),--(B2:B20="DOOLEYS SYD"),C2:C20)

etc.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"HayleyKingston" wrote in message
...
Hi,

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

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

welcome, glad it worked out fine for you
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"HayleyKingston" wrote in message
...
This was most helpful and is how I have worked it all out. Thank you so
much