Combining Filter and AverageIfs

[beedistinct]

I wish to filter my data using the macro and then apply the AverageIfs function only on the filtered data. I've no idea how to restrict the AverageIfs function just to the data that is filtered.
The below is what I have so far. However, The AverageRange is the entire Range and not the filtered rows only.

Dim AverageCount As Long
 
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$14").AutoFilter Field:=2, Criteria1:="Active"
    Set AverageRange = Range("A1:A13")
    Set CriteriaRange = Range("C1:C13")
    AverageCount = Application.AverageIfs(AverageRange, CriteriaRange, "<=1/11/2013")
    
    
    MsgBox ("Average of Active cells is : " & AverageCount)

 

[T. Valko]

I wish to filter my data using the macro and then apply the AverageIfs function only on the filtered data. I've no idea how to restrict the AverageIfs function just to the data that is filtered.
The below is what I have so far. However, The AverageRange is the entire Range and not the filtered rows only.

Dim AverageCount As Long
 
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$14").AutoFilter Field:=2, Criteria1:="Active"
    Set AverageRange = Range("A1:A13")
    Set CriteriaRange = Range("C1:C13")
    AverageCount = Application.AverageIfs(AverageRange, CriteriaRange, "<=1/11/2013")
 
 
    MsgBox ("Average of Active cells is : " & AverageCount)

Here's the array formula** that would be entered on the worksheet:

=AVERAGE(IF(SUBTOTAL(2,OFFSET(A1,ROW(A1:A13)-ROW(A1),0,1)),IF(C1:C13<=DATE(2013,11,1),A1:A13)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Maybe you can figure out how to write it into your code.

I assume the date was in the format of d/m/y.

 

[Domenic]

Using Biff's approach, maybe something like this...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Avg [color=darkblue]As[/color] [color=darkblue]Double[/color]
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [color=darkblue]With[/color] Range("A1:C" & LastRow)
        .AutoFilter field:=2, Criteria1:="Active"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Avg = Application.Evaluate("AVERAGE(IF(SUBTOTAL(3,OFFSET(A2:A" & LastRow & _
                ",ROW(A2:A" & LastRow & ")-ROW(A2),0,1))>0,IF(C2:C" & LastRow & _
                "<=DATE(2013,11,1),A2:A" & LastRow & ")))")
    
    MsgBox "The average is:  " & Avg
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

To clear the filter, the following line of code can be added...

ActiveSheet.AutoFilterMode = False

Note that the formula will need to be amended if Column C and/or Column A contain empty cells.

 

[beedistinct]

Thank you for the guidance!! I do not understand how the array formula works.

Could you please point me to some reference material that can help me understand/interpret array formulas?

 

[T. Valko]

Thank you for the guidance!! I do not understand how the array formula works.

Could you please point me to some reference material that can help me understand/interpret array formulas?

See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[beedistinct]

Great! Thank you

 

[T. Valko]

Great! Thank you

You're welcome!