I have a workbook with 3 sheets. The 1st sheet has my raw source data, dumped
from MS Access. The 2nd sheet has a table of averages dynamically set based
on the source data and the 3rd sheet has a chart based on my average data.

I would like to implement some way to have my average data change based on
filtering of my source data.

Currently, I am using the following 2 formulas in my average table.
=IF(SUMPRODUCT(--(AAT_Raw_Data!$A$1:AAT_Raw_Data!$A$20000=$A2)),SUM PRODUCT(--(AAT_Raw_Data!$A$1:AAT_Raw_Data!$A$20000=$A2),AAT_ Raw_Data!$B$1:AAT_Raw_Data!$B$20000)/SUMPRODUCT(--(AAT_Raw_Data!$A$1:AAT_Raw_Data!$A$20000=$A2)),0)
=IF(SUMPRODUCT(--(AAT_Raw_Data!$A$1:AAT_Raw_Data!$A$20000=$A2),--(AAT_Raw_Data!C$1:AAT_Raw_Data!C$20000=1)),SUMPROD UCT(--(AAT_Raw_Data!$A$1:AAT_Raw_Data!$A$20000=$A2),--(AAT_Raw_Data!C$1:AAT_Raw_Data!C$20000=1),AAT_Raw_ Data!$B$1:AAT_Raw_Data!$B$20000)/SUMPRODUCT(--(AAT_Raw_Data!$A$1:AAT_Raw_Data!$A$20000=$A2),--(AAT_Raw_Data!C$1:AAT_Raw_Data!C$20000=1)),0)

Is there some way to have these formulas only "pick up" data from my source
sheet that is filtered with some sort of filtering?

I actually prefer to give my users the ability to only filter on certain
columns on my source data, so using a filtering option other than the auto
filter is better.

So I guess my question is two-fold. What options do I have to create filters
and how do I get the formulas above to only calculate on filtered data?

BTW, I tried to figure out a way to use a Pivot Chart, since it allows for
filtering, but I can't figure out a way to use my "AAT_Raw_Data" tab for only
filtering options and my "AAT_Avg_Data" tab for the series. BTW, I have 5
series on a combo line/column chart.

Thank you,
Clint