| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIFS BY MONTH
Workbook Sales
(A1)Product (B1)Qty (C1)Date X 2 2/1/2010 X 1 10/1/2010 X 3 5/3/2010 Y 1 8/1/2010 Y 2 5/2/2010 Y 1 3/3/2010 Y 1 5/3/2010 Z 2 3/2/2010 Z 1 5/3/2010 Workbook Report SUMQTY (A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10 X 3 0 3 Y 2 2 1 Z 1 2 0 Hi Sales is a excel workbook from our sales system. I would like some code to organise all the sales data found in Sales and group it into monthly columns in a new excel file Report but being new to vba I dont know where to start. In the workbook Report: I want B2 to be the current month and year (Mar10), C2 to be the current month -1 (Feb10) and so on until I have 12 columns i.e 1 year. Then I want the SUM of all the QTYs for each month for each product in the correct column as shown above. Can anyone help with the code? A pivot table is not the answer because I also have other data which I want to pull in from other external workbooks Many thanks Simon |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMIFS BY MONTH
Hi Simon
It would have been better to stay in the same thread, so people could see what replies you have already had. Personally, I would still use a PT to do the "heavy lifting" for me in terms of calculating the results. I would then pull results from the PT (which could be on a hidden sheet) to my main report, along with the other data you wish to collate. In your case, the PT would have a fixed width , but potentially a growing number of rows, so there would not be a need to use the slightly complicated GetPivotData function to extract your results, you could simple use Index and Match. Supposing your Pt was on a sheet called "PT" then on your Report sheet, with your products starting in A2 downward and your Months in B1 onward, the formula in B2 would be =INDEX(PT!$1:$65536,MATCH($A2,PT!$A:$A,0),MATCH(B$ 1,PT!$4:$4,0)) This would be copied across and down as required. Any other data could be inserted on the report page below this. But if you do want to do it the hard way <bg!!!! Then Sumproduct would be the way, but be aware that on large datasets, Sumproduct can be very slow. I would definitely use Dynamic Named ranges for the Sumproduct formulae, rather than over long ranges just to allow for more data entry, as this will limit the number of calculations Sumproduct has to make. Rather than describe the method here in detail, take a look at the tutorial I wrote at http://www.contextures.com/xlNames03.html with a sample downloadable file http://www.contextures.com/CreateNames.zip This should show you how to do it with both Sumproduct and PT's If you need more help, post back (in the same thread) and also include the version of Excel you are using. Hope this helps. -- Regards Roger Govier Simon wrote: Workbook Sales (A1)Product (B1)Qty (C1)Date X 2 2/1/2010 X 1 10/1/2010 X 3 5/3/2010 Y 1 8/1/2010 Y 2 5/2/2010 Y 1 3/3/2010 Y 1 5/3/2010 Z 2 3/2/2010 Z 1 5/3/2010 Workbook Report SUMQTY (A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10 X 3 0 3 Y 2 2 1 Z 1 2 0 Hi Sales is a excel workbook from our sales system. I would like some code to organise all the sales data found in Sales and group it into monthly columns in a new excel file Report but being new to vba I dont know where to start. In the workbook Report: I want B2 to be the current month and year (Mar10), C2 to be the current month -1 (Feb10) and so on until I have 12 columns i.e 1 year. Then I want the SUM of all the QTYs for each month for each product in the correct column as shown above. Can anyone help with the code? A pivot table is not the answer because I also have other data which I want to pull in from other external workbooks Many thanks Simon |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMIFS BY MONTH
Hi Roger
Thanks again for your help. I have never used pivot tables before thus my hesitation. But on your advice I will give it a go. My version is 2007. I might need a bit of guidance getting the pivot table created. As I understand it I need to create the pivot table on another sheet within Reports. Using the data in Sales as an external source? The PT should have a dynamic range? Cheers Simon "Roger Govier" wrote: Hi Simon It would have been better to stay in the same thread, so people could see what replies you have already had. Personally, I would still use a PT to do the "heavy lifting" for me in terms of calculating the results. I would then pull results from the PT (which could be on a hidden sheet) to my main report, along with the other data you wish to collate. In your case, the PT would have a fixed width , but potentially a growing number of rows, so there would not be a need to use the slightly complicated GetPivotData function to extract your results, you could simple use Index and Match. Supposing your Pt was on a sheet called "PT" then on your Report sheet, with your products starting in A2 downward and your Months in B1 onward, the formula in B2 would be =INDEX(PT!$1:$65536,MATCH($A2,PT!$A:$A,0),MATCH(B$ 1,PT!$4:$4,0)) This would be copied across and down as required. Any other data could be inserted on the report page below this. But if you do want to do it the hard way <bg!!!! Then Sumproduct would be the way, but be aware that on large datasets, Sumproduct can be very slow. I would definitely use Dynamic Named ranges for the Sumproduct formulae, rather than over long ranges just to allow for more data entry, as this will limit the number of calculations Sumproduct has to make. Rather than describe the method here in detail, take a look at the tutorial I wrote at http://www.contextures.com/xlNames03.html with a sample downloadable file http://www.contextures.com/CreateNames.zip This should show you how to do it with both Sumproduct and PT's If you need more help, post back (in the same thread) and also include the version of Excel you are using. Hope this helps. -- Regards Roger Govier Simon wrote: Workbook âœSalesâ (A1)Product (B1)Qty (C1)Date X 2 2/1/2010 X 1 10/1/2010 X 3 5/3/2010 Y 1 8/1/2010 Y 2 5/2/2010 Y 1 3/3/2010 Y 1 5/3/2010 Z 2 3/2/2010 Z 1 5/3/2010 Workbook âœReportâ SUMQTY (A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10 X 3 0 3 Y 2 2 1 Z 1 2 0 Hi âœSalesâ is a excel workbook from our sales system. I would like some code to organise all the sales data found in âœSalesâ and group it into monthly columns in a new excel file âœReportâ but being new to vba I donât know where to start. In the workbook âœReportâ: I want B2 to be the current month and year (Mar10), C2 to be the current month -1 (Feb10) and so on until I have 12 columns i.e 1 year. Then I want the SUM of all the QTYs for each month for each product in the correct column as shown above. Can anyone help with the code? A pivot table is not the answer because I also have other data which I want to pull in from other external workbooks Many thanks Simon . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMIFS BY MONTH
Would it be easier if I supplied my xls file to better explain? Is this
possible through the forum? "Simon" wrote: Hi Roger Thanks again for your help. I have never used pivot tables before thus my hesitation. But on your advice I will give it a go. My version is 2007. I might need a bit of guidance getting the pivot table created. As I understand it I need to create the pivot table on another sheet within Reports. Using the data in Sales as an external source? The PT should have a dynamic range? Cheers Simon "Roger Govier" wrote: Hi Simon It would have been better to stay in the same thread, so people could see what replies you have already had. Personally, I would still use a PT to do the "heavy lifting" for me in terms of calculating the results. I would then pull results from the PT (which could be on a hidden sheet) to my main report, along with the other data you wish to collate. In your case, the PT would have a fixed width , but potentially a growing number of rows, so there would not be a need to use the slightly complicated GetPivotData function to extract your results, you could simple use Index and Match. Supposing your Pt was on a sheet called "PT" then on your Report sheet, with your products starting in A2 downward and your Months in B1 onward, the formula in B2 would be =INDEX(PT!$1:$65536,MATCH($A2,PT!$A:$A,0),MATCH(B$ 1,PT!$4:$4,0)) This would be copied across and down as required. Any other data could be inserted on the report page below this. But if you do want to do it the hard way <bg!!!! Then Sumproduct would be the way, but be aware that on large datasets, Sumproduct can be very slow. I would definitely use Dynamic Named ranges for the Sumproduct formulae, rather than over long ranges just to allow for more data entry, as this will limit the number of calculations Sumproduct has to make. Rather than describe the method here in detail, take a look at the tutorial I wrote at http://www.contextures.com/xlNames03.html with a sample downloadable file http://www.contextures.com/CreateNames.zip This should show you how to do it with both Sumproduct and PT's If you need more help, post back (in the same thread) and also include the version of Excel you are using. Hope this helps. -- Regards Roger Govier Simon wrote: Workbook âœSalesâ (A1)Product (B1)Qty (C1)Date X 2 2/1/2010 X 1 10/1/2010 X 3 5/3/2010 Y 1 8/1/2010 Y 2 5/2/2010 Y 1 3/3/2010 Y 1 5/3/2010 Z 2 3/2/2010 Z 1 5/3/2010 Workbook âœReportâ SUMQTY (A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10 X 3 0 3 Y 2 2 1 Z 1 2 0 Hi âœSalesâ is a excel workbook from our sales system. I would like some code to organise all the sales data found in âœSalesâ and group it into monthly columns in a new excel file âœReportâ but being new to vba I donât know where to start. In the workbook âœReportâ: I want B2 to be the current month and year (Mar10), C2 to be the current month -1 (Feb10) and so on until I have 12 columns i.e 1 year. Then I want the SUM of all the QTYs for each month for each product in the correct column as shown above. Can anyone help with the code? A pivot table is not the answer because I also have other data which I want to pull in from other external workbooks Many thanks Simon . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMIFS BY MONTH
Hi Simon
Yes, I would use a dynamic named range for a PT source http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Simon" wrote in message ... Hi Roger Thanks again for your help. I have never used pivot tables before thus my hesitation. But on your advice I will give it a go. My version is 2007. I might need a bit of guidance getting the pivot table created. As I understand it I need to create the pivot table on another sheet within Reports. Using the data in Sales as an external source? The PT should have a dynamic range? Cheers Simon "Roger Govier" wrote: Hi Simon It would have been better to stay in the same thread, so people could see what replies you have already had. Personally, I would still use a PT to do the "heavy lifting" for me in terms of calculating the results. I would then pull results from the PT (which could be on a hidden sheet) to my main report, along with the other data you wish to collate. In your case, the PT would have a fixed width , but potentially a growing number of rows, so there would not be a need to use the slightly complicated GetPivotData function to extract your results, you could simple use Index and Match. Supposing your Pt was on a sheet called "PT" then on your Report sheet, with your products starting in A2 downward and your Months in B1 onward, the formula in B2 would be =INDEX(PT!$1:$65536,MATCH($A2,PT!$A:$A,0),MATCH(B$ 1,PT!$4:$4,0)) This would be copied across and down as required. Any other data could be inserted on the report page below this. But if you do want to do it the hard way <bg!!!! Then Sumproduct would be the way, but be aware that on large datasets, Sumproduct can be very slow. I would definitely use Dynamic Named ranges for the Sumproduct formulae, rather than over long ranges just to allow for more data entry, as this will limit the number of calculations Sumproduct has to make. Rather than describe the method here in detail, take a look at the tutorial I wrote at http://www.contextures.com/xlNames03.html with a sample downloadable file http://www.contextures.com/CreateNames.zip This should show you how to do it with both Sumproduct and PT's If you need more help, post back (in the same thread) and also include the version of Excel you are using. Hope this helps. -- Regards Roger Govier Simon wrote: Workbook âœSalesâ (A1)Product (B1)Qty (C1)Date X 2 2/1/2010 X 1 10/1/2010 X 3 5/3/2010 Y 1 8/1/2010 Y 2 5/2/2010 Y 1 3/3/2010 Y 1 5/3/2010 Z 2 3/2/2010 Z 1 5/3/2010 Workbook âœReportâ SUMQTY (A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10 X 3 0 3 Y 2 2 1 Z 1 2 0 Hi âœSalesâ is a excel workbook from our sales system. I would like some code to organise all the sales data found in âœSalesâ and group it into monthly columns in a new excel file âœReportâ but being new to vba I donât know where to start. In the workbook âœReportâ: I want B2 to be the current month and year (Mar10), C2 to be the current month -1 (Feb10) and so on until I have 12 columns i.e 1 year. Then I want the SUM of all the QTYs for each month for each product in the correct column as shown above. Can anyone help with the code? A pivot table is not the answer because I also have other data which I want to pull in from other external workbooks Many thanks Simon . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMIFS BY MONTH
OK
I have created a pivot table in PT and grouped the dates into months It looks like this: SumofQtySold Item Date Total WidgetX Jan 300 Mar 400 Jun 1200 WidgetY Jan 2000 Feb 800 etc I have tried the formula: Supposing your Pt was on a sheet called "PT" then on your Report sheet, with your products starting in A4downward and your Months in K3 onward, the formula in K4 would be =INDEX(PT!$1:$65536,MATCH($A4,PT!$A:$A,0),MATCH(K$ 3,PT!$4:$4,0)) But get a #N/A What am I doing wrong? "ozgrid.com" wrote: Hi Simon Yes, I would use a dynamic named range for a PT source http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Simon" wrote in message ... Hi Roger Thanks again for your help. I have never used pivot tables before thus my hesitation. But on your advice I will give it a go. My version is 2007. I might need a bit of guidance getting the pivot table created. As I understand it I need to create the pivot table on another sheet within Reports. Using the data in Sales as an external source? The PT should have a dynamic range? Cheers Simon "Roger Govier" wrote: Hi Simon It would have been better to stay in the same thread, so people could see what replies you have already had. Personally, I would still use a PT to do the "heavy lifting" for me in terms of calculating the results. I would then pull results from the PT (which could be on a hidden sheet) to my main report, along with the other data you wish to collate. In your case, the PT would have a fixed width , but potentially a growing number of rows, so there would not be a need to use the slightly complicated GetPivotData function to extract your results, you could simple use Index and Match. Supposing your Pt was on a sheet called "PT" then on your Report sheet, with your products starting in A2 downward and your Months in B1 onward, the formula in B2 would be =INDEX(PT!$1:$65536,MATCH($A2,PT!$A:$A,0),MATCH(B$ 1,PT!$4:$4,0)) This would be copied across and down as required. Any other data could be inserted on the report page below this. But if you do want to do it the hard way <bg!!!! Then Sumproduct would be the way, but be aware that on large datasets, Sumproduct can be very slow. I would definitely use Dynamic Named ranges for the Sumproduct formulae, rather than over long ranges just to allow for more data entry, as this will limit the number of calculations Sumproduct has to make. Rather than describe the method here in detail, take a look at the tutorial I wrote at http://www.contextures.com/xlNames03.html with a sample downloadable file http://www.contextures.com/CreateNames.zip This should show you how to do it with both Sumproduct and PT's If you need more help, post back (in the same thread) and also include the version of Excel you are using. Hope this helps. -- Regards Roger Govier Simon wrote: Workbook âœSalesâ (A1)Product (B1)Qty (C1)Date X 2 2/1/2010 X 1 10/1/2010 X 3 5/3/2010 Y 1 8/1/2010 Y 2 5/2/2010 Y 1 3/3/2010 Y 1 5/3/2010 Z 2 3/2/2010 Z 1 5/3/2010 Workbook âœReportâ SUMQTY (A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10 X 3 0 3 Y 2 2 1 Z 1 2 0 Hi âœSalesâ is a excel workbook from our sales system. I would like some code to organise all the sales data found in âœSalesâ and group it into monthly columns in a new excel file âœReportâ but being new to vba I donât know where to start. In the workbook âœReportâ: I want B2 to be the current month and year (Mar10), C2 to be the current month -1 (Feb10) and so on until I have 12 columns i.e 1 year. Then I want the SUM of all the QTYs for each month for each product in the correct column as shown above. Can anyone help with the code? A pivot table is not the answer because I also have other data which I want to pull in from other external workbooks Many thanks Simon . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMIFS BY MONTH
This seems to work though
Range("K4").Select ActiveCell.FormulaR1C1 = _ "=IFERROR(GETPIVOTDATA(""Qty Sold"",PT!R1C1,""Item"",RC1,""Actual Fulfillment Date"",MONTH(R3C)),0)" Selection.AutoFill Destination:=Range("K4:K" & LR), Type:=xlFillDefault And is way faster than my old sumproduct method. Now I just have to figure out creating a dynamic range for the Pivot table. "Simon" wrote: OK I have created a pivot table in PT and grouped the dates into months It looks like this: SumofQtySold Item Date Total WidgetX Jan 300 Mar 400 Jun 1200 WidgetY Jan 2000 Feb 800 etc I have tried the formula: Supposing your Pt was on a sheet called "PT" then on your Report sheet, with your products starting in A4downward and your Months in K3 onward, the formula in K4 would be =INDEX(PT!$1:$65536,MATCH($A4,PT!$A:$A,0),MATCH(K$ 3,PT!$4:$4,0)) But get a #N/A What am I doing wrong? "ozgrid.com" wrote: Hi Simon Yes, I would use a dynamic named range for a PT source http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Simon" wrote in message ... Hi Roger Thanks again for your help. I have never used pivot tables before thus my hesitation. But on your advice I will give it a go. My version is 2007. I might need a bit of guidance getting the pivot table created. As I understand it I need to create the pivot table on another sheet within Reports. Using the data in Sales as an external source? The PT should have a dynamic range? Cheers Simon "Roger Govier" wrote: Hi Simon It would have been better to stay in the same thread, so people could see what replies you have already had. Personally, I would still use a PT to do the "heavy lifting" for me in terms of calculating the results. I would then pull results from the PT (which could be on a hidden sheet) to my main report, along with the other data you wish to collate. In your case, the PT would have a fixed width , but potentially a growing number of rows, so there would not be a need to use the slightly complicated GetPivotData function to extract your results, you could simple use Index and Match. Supposing your Pt was on a sheet called "PT" then on your Report sheet, with your products starting in A2 downward and your Months in B1 onward, the formula in B2 would be =INDEX(PT!$1:$65536,MATCH($A2,PT!$A:$A,0),MATCH(B$ 1,PT!$4:$4,0)) This would be copied across and down as required. Any other data could be inserted on the report page below this. But if you do want to do it the hard way <bg!!!! Then Sumproduct would be the way, but be aware that on large datasets, Sumproduct can be very slow. I would definitely use Dynamic Named ranges for the Sumproduct formulae, rather than over long ranges just to allow for more data entry, as this will limit the number of calculations Sumproduct has to make. Rather than describe the method here in detail, take a look at the tutorial I wrote at http://www.contextures.com/xlNames03.html with a sample downloadable file http://www.contextures.com/CreateNames.zip This should show you how to do it with both Sumproduct and PT's If you need more help, post back (in the same thread) and also include the version of Excel you are using. Hope this helps. -- Regards Roger Govier Simon wrote: Workbook âœSalesâ (A1)Product (B1)Qty (C1)Date X 2 2/1/2010 X 1 10/1/2010 X 3 5/3/2010 Y 1 8/1/2010 Y 2 5/2/2010 Y 1 3/3/2010 Y 1 5/3/2010 Z 2 3/2/2010 Z 1 5/3/2010 Workbook âœReportâ SUMQTY (A2)Product (B2)Mar10 (C2)Feb10 (D2)Jan10 X 3 0 3 Y 2 2 1 Z 1 2 0 Hi âœSalesâ is a excel workbook from our sales system. I would like some code to organise all the sales data found in âœSalesâ and group it into monthly columns in a new excel file âœReportâ but being new to vba I donât know where to start. In the workbook âœReportâ: I want B2 to be the current month and year (Mar10), C2 to be the current month -1 (Feb10) and so on until I have 12 columns i.e 1 year. Then I want the SUM of all the QTYs for each month for each product in the correct column as shown above. Can anyone help with the code? A pivot table is not the answer because I also have other data which I want to pull in from other external workbooks Many thanks Simon . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why can't I use the MONTH function within the SUMIFS statement? | Excel Worksheet Functions | |||
| SUMIFS | Excel Discussion (Misc queries) | |||
| SUMIFS-time criteria/month | Excel Worksheet Functions | |||
| Using Sumifs | Excel Worksheet Functions | |||
| using sumifs to sum based on month, and criteria | Excel Worksheet Functions | |||
Hybrid Mode
