calculating column amounts
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 |
calculating column amounts
=SUMPRODUCT(--(YEAR(A2:A200)=2008),--(MONTH(A2:A200)=10),B2:B200)
for Oct 2008. -- __________________________________ HTH Bob "wilksy" wrote in message ... 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 |
calculating column amounts
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 "Bob Phillips" wrote: =SUMPRODUCT(--(YEAR(A2:A200)=2008),--(MONTH(A2:A200)=10),B2:B200) for Oct 2008. -- __________________________________ HTH Bob "wilksy" wrote in message ... 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 |
calculating column amounts
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/assistan...lconPT101.aspx wilksy wrote: 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 "Bob Phillips" wrote: =SUMPRODUCT(--(YEAR(A2:A200)=2008),--(MONTH(A2:A200)=10),B2:B200) for Oct 2008. -- __________________________________ HTH Bob "wilksy" wrote in message ... 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 -- Dave Peterson |
calculating column amounts
Thanks Dave
With a little tweaking it worked a treat! Regards "Dave Peterson" wrote: 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/assistan...lconPT101.aspx wilksy wrote: 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 "Bob Phillips" wrote: =SUMPRODUCT(--(YEAR(A2:A200)=2008),--(MONTH(A2:A200)=10),B2:B200) for Oct 2008. -- __________________________________ HTH Bob "wilksy" wrote in message ... 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 -- Dave Peterson |
All times are GMT +1. The time now is 01:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com