Weekly/Monthly Averages

I have a worksheet which is populated with weekly data for a range of items. Each week is a seperate row and each item is a seperate column. Each row has a date in column A. The data spans several years.

I need to calculate a monthly average for each item. For example, for a single item I need to average all the "Jan-04" rows, etc.

I have tried using "If", "vlookup", "Sumif", etc. nothing seems to give me what I am looking for.

I think I'm close with SumIf (using it with a CountIf as a divisor) but I can't seem to get it right.

Any advice?

Keith

Keith,

You could use this array formula,

=AVERAGE(IF(MONTH(A2:A13)=1,IF(YEAR(A2:A13)=2004,B2:B13)))

Commit with Ctrl+Shift+Enter not just enter. If you'd rather not use an array try,

=SUMPRODUCT((MONTH(A2:A13)=1)*(YEAR(A2:A13)=2004)*(B2:B13))/SUMPRODUCT((MONTH(A2:A13)=1)*(YEAR(A2:A13)=2004))

HTH

Steve

Thanks Steve,

I couldn't get the array formula to work but was able to get the SUMPRODUCT formula to work perfectly.

Any way to use the worksheet name as a variable? In other words, I want to use "sheet1" OR the exact same formatted data on "sheet2" in the same formula depending on the product I chose?

Keith