Best way to calculate MTD

[r!chg]

Afternoon All,

I have a list of data as Follows for year 2011 and 2010
<table style="width: 507px; height: 100px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 132pt;" width="176"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 56pt;" width="75"> <col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 132pt;" height="20" width="176"> <table style="width: 331px; height: 100px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="2" width="64"> <col style="width: 56pt;" width="75"> <col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" height="20" width="64">Month</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Week</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Fruit</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Volume</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Mar</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">banana</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">48</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Mar</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Orange</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1562</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Mar</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">nectarine</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">519</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Mar</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">nuts</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> </tbody></table></td><td class="xl63" style="border-left: medium none; width: 48pt;" width="64">
</td><td class="xl63" style="border-left: medium none; width: 48pt;" width="64">
</td><td class="xl63" style="border-left: medium none; width: 56pt;" width="75">
</td><td class="xl63" style="border-left: medium none; width: 48pt;" width="64">
</td><td class="xl63" style="border-left: medium none; width: 48pt;" width="64">
</td></tr></tbody></table>
Currently I have the following function to sum volume according to week and Fruit.
=SUMPRODUCT(--(Data!$B$6:$B$2000=$B$2),--(Data!$E$6:$E$2000="nectarine"),Data!$F$6:$F$2000)

I would like to compare Month to date for 2011 and 2010. Adding an array to the above formula for month is easy enough. But I need to be able to add the 2010 data according to the weeks in 2011. So if its only Weeks 10,11,12 for Mar2011 can I look for the comparative data in the 2010 sheet?

I was wondering if a dynamic range would work but my knowledge of this is very limited.

Any assistance would be greatly appreciated

 

[texasalynn]

try this

Excel Workbook
	A	B	C	D	E	F
1	Date	Month	Year	Week	Fruit	Volume
2	3/5/2010	Mar	2010	10	banana	38
3	3/5/2010	Mar	2010	10	Orange	1550
4	3/5/2010	Mar	2010	10	nectarine	500
5	3/5/2010	Mar	2010	10	nuts	5
6	3/5/2011	Mar	2011	10	banana	48
7	3/5/2011	Mar	2011	10	Orange	1562
8	3/5/2011	Mar	2011	10	nectarine	519
9	3/5/2011	Mar	2011	10	nuts	1
Data

Excel Workbook
	B	C
2	Mar-2011	519
3	week 10-12	519
Excel 2003 Sheet2

Excel 2003

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--(MONTH(Data!$A$6:$A$2000)=MONTH($B$2)),--(Data!$E$6:$E$2000="nectarine"),Data!$F$6:$F$2000)
C3	=SUMPRODUCT(--(MONTH(Data!$A$6:$A$2000)=MONTH($B$2)),--(YEAR(Data!$A$6:$A$2000)=YEAR($B$2)),--(Data!$D$6:$D$2000>=10),--(Data!$D$6:$D$2000<=12),--(Data!$E$6:$E$2000="nectarine"),Data!$F$6:$F$2000)

 

[r!chg]

Thank you for your solution.

I understand this to work for a static view. For each of the weeks.

I realise now the result im looking for is for the MTD to roll on for each week within the month. Accumulating a month to date total as the weeks in the month continue.

As March as an Example
Calculate Nectarines week 10 volume as a total for the first week in march then calculate the volume for week 10 + week 11 as a total for the first 2 weeks in Mar and so on until you get to the 14th week in march which is a sum of the volume from week 10 to 14. Then Do the same for the other fruits.

I can only think of doing this as a seperate column in the data sheet. Or reorganising the data.

I thought it might be possible with a dynamic range.

(Need to learn how best to show my ideas or examples)

 

[r!chg]

So I have found a solution to calculate Month to Date.

Note: Cell reference in the MTD! can be changed by the User. So they can report on any month they wish.

=SUM(OFFSET(DataDay2011!$B$3,MATCH(MTD!$C$3,DataDay2011!$A$4:$A$368,FALSE),4,SUM($B$3-VLOOKUP(MTD!$C$3,DataDay2011!$A$3:$B$368,2,FALSE),FALSE)))

The match function is looking for a month to report on.
4 is a reference to a column.
The Vlookup references the date on which to start calculating.
We report using a 4 week, 4 week, 5 Week basis. So the reporting period may start on the 2nd of the month rather then the 1st. The Vlookup will search for the month and then return the start date. I have had to manually enter the Jan to Dec next to their respective start dates in the Dataday2011!

I think there may be a way to substitute COUNT for the Vlookup but that will be for another day.