Formula Help - Not sure which one I need! SUMIF I think?!

[HayleyKingston]

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

 

[Gary''s Student]

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

 

[HayleyKingston]

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?

 

[Max]

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

 

[Bob Phillips]

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]

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

 

[Max]

welcome, glad it worked out fine for you