SUMIFS BY MONTH

[Simon]

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

 

[Roger Govier]

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.

 

[Simon]

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

 

[Simon]

Would it be easier if I supplied my xls file to better explain? Is this
possible through the forum?

 

[ozgrid.com]

Hi Simon

Yes, I would use a dynamic named range for a PT source
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Simon]

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?

 

[Simon]

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.