Sumproduct with Multiple Criteria to a calculate a moving average based on a range

Hi, I am trying to use sumproduct to calculate a moving average matching a set of criteria. Here is my formula and i am attaching a version of the excel workbook. I am using excel 2010.

=SUMPRODUCT(--('Weekly Vol Pivot'!$B$12:$B$300>=(MAX(IF('Weekly Vol Pivot'!$B$12:$B$300=CONCATENATE(VLOOKUP($D$5,Lookup!$C$17:$D$28,2,FALSE),$D$6),'Weekly Vol Pivot'!$B$12:$B$300))-42)),--(('Weekly Vol Pivot'!$B$12:$B$300= CONCATENATE(VLOOKUP($D$5,Lookup!$C$17:$D$28,2,FALSE),$D$6))),--('Weekly Vol Pivot'!$N$12:$N$300))/6

Please help.

Maybe something like this (pivot table)

Originally Posted by dilipandey

Hi Krunk,

Where to look in the workbook.... Which cell has this formula?

Regards,
DILIPandey

<click on below 'star' if this helps>

It is cell d21, and also the one that has the #N/A formula.

Does anyone have any suggestions? I really need this formula to work?

The answer should be to find the sum of the previous 3 (06week4) from a range then sum them then divide by 3 to provide the average. So on the tab labeled "weekly vol Pivot" column M holds the (month&Week combination) and n has the values that i would like to sum. I need it to find the previous 3 values that match the (month&Week combination) excluding the most recent match and divide by 3. Hopefully this makes more sense. I can't change the pivot becuase i have other values that are linked to it. I want to put the formula on the "summary" tab in cell 21 which right now hold an #N/A with the formula that i tried. Any help or insight that you can provide would be greatly appreciated. Just in case here is the formula again..

=SUMPRODUCT(--('Weekly Vol Pivot'!$B$12:$B$300>=(MAX(IF('Weekly Vol Pivot'!$B$12:$B$300=CONCATENATE(VLOOKUP($D$5,Lookup!$C$17:$D$28,2,FALSE),$D$6),'Weekly Vol Pivot'!$B$12:$B$300))-21)),--(('Weekly Vol Pivot'!$B$12:$B$300= CONCATENATE(VLOOKUP($D$5,Lookup!$C$17:$D$28,2,FALSE),$D$6))),--('Weekly Vol Pivot'!$N$12:$N$300))/3

I can't changed the Pivot Table since i have other cells referencing the table. Plus, id really like to use this formula so that every month i don't have to be constantly updating the formula or lookup's.

Hoping you can help me.

Thanks again!

Please, does anyone have any other suggestions or advice? Or know of another forum that can help?