Custom filters that filter formula results?
 

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

Custom filters that filter formula results?
 

Here is a way to change an existing SUMPRODUCT() formula to work on filtered
data. For example in column A from A2 thru A100 we have animals - dogs,
cats, birds, snakes, etc. In column B we have either small medium or large.
If we want to count the number of large dogs, we might use:

=SUMPRODUCT((A2:A100="dog")*(B2:B100="large"))

If, however, the data is filtered (with AutoFilter), we can use:

=SUMPRODUCT((A2:A100="dog")*(B2:B100="large")*(vis i(B2:B100))

visi is the following UDF:

Public Function visi(rr As Range) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''
' rr must be a column or piece of a column
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''
Application.Volatile
Dim r As Range
Dim v()
ReDim v(1 To rr.Count)
i = 1
For Each r In rr
If r.EntireRow.Hidden = False Then
v(i) = 1
Else
v(i) = 0
End If
i = i + 1
Next
visi = Application.Transpose(v)
End Function

This is an array function that returns 0 if the row is hidden.
--
Gary''s Student - gsnu200909


"cherman" wrote:

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