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")*(visi(B2:B100))
visi is the following UDF:
Public Function visi(rr As Range) As Varian
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' rr must be a column or piece of a colum
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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)),SUMPRODUCT(--(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)),SUMPRODUCT(--(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
|
Similar Threads
