krunk,
Attached is a modified version of your posted workbook. I created a new sheet (which you can hide) in order to have a helper column. I didn't use a helper column on the 'Daily Summary' sheet because of the way it was formatted (small rows, hidden rows, etc).
On the new sheet (named 'Sheet1') in cell A1 is this formula to get the column B criteria:
That formula results in "06Week 3" which is what we need to match against in column B of the 'Weekly Vol Pivot' sheet.
Then in 'Sheet1' cell A2 and copied down is this array formula. Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter:
That formula will pull the bottom 6 values that match the criteria (if they exist). Because there are only 3 entries in column B that are "06Week 3" there are only 3 results returned. They also happen to all be the same value, but it does return them in reverse order (bottom listed first). Once it runs out of matches, the formula returns a blank to prevent errors with the Average function.
Then, to get the average, in the 'Daily Summary' sheet cell D21 is this formula:
Bookmarks