calculating column amounts

[wilksy]

I have 2 columns, 1 contains an item code and the other the qty booked out
against that code number. The items are booked out during the day and
entered as they occur. I would like to enter a formula that calulates how
many times during a month that item code number occurs and what the total qty
used in that month.

I am using the COUNTIF function but that will only give me the amount of
times a particular code number has been used.

Can anybody suggest a suitable formula.

Thanks

 

[Bob Phillips]

=SUMPRODUCT(--(YEAR(A2:A200)=2008),--(MONTH(A2:A200)=10),B2:B200)

for Oct 2008.

 

[wilksy]

Thanks Bob

I think I might have misled you with the term month i have the following:

A B
Code Qty
5 10
7 1
14 2
23 3
34 4
45 7
5 6
14 12

I need a formula that will calculate the amount of times the code number
appears in column A and the qty of that code number used.

Thanks

 

[Dave Peterson]

Maybe...
=sumif(a:a,5,b:b)
To sum the quantities that have 5 as the code.

This looks like a reason to learn about pivottables, too.

In xl2003 menus:
Select the range (A1:B###)
Data|Pivottable
Follow the wizard until you get to a step with a Layout Button on it.
Click that layout button
Drag the header for the Code to the row area
Drag the header for the Qty to the Data area
(if it doesn't say sum of, rightclick on it and choose Sum)
and finish up the wizard.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[wilksy]

Thanks Dave

With a little tweaking it worked a treat!

Regards