Multiple AverageIf in Excel

In Excel 2007 and later you can use the AVERAGEIFS function, like so:

=AVERAGEIFS(C2:C5, A2:A5, "A", B2:B5, "D")

You could always use the inbuilt VBA language. I just tried this and it works - YMMV.

Go to Tools | Macro | Visual Basic Editor.

A new window will open.

Go to Tools | References. Click the checkbox beside Select Microsoft ActiveX Data Objects 2.5 Library. Click Ok.

Double click Sheet1 from the list over to the left. (The Project Explorer) - your sheet may be named something differently.

Copy and paste the following code into the code window (the window with the two dropdown lists at the top)

See the highlighted line below - edit it to make sure you define the full path to your Excel workbook. ie:

sXLSFile = "s:\brad\book1.xls"

Click the Run menu, then click the Run Sub/Userform (F5) option.

All things being equal (!), you should get a message box pop up with the average of the selected rows - see the 2nd highlighted line below which actually runs the query.

.Open "**select avg(value) from [sheet1$] where c1='A' and c2='D'**"

You just need to change the above line to change the query - eg if you wanted the average where C1 = "B" and C2 = "A"

Sub Main()

    Dim sXLSFile As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    ' set the location of the Excel worksheet
    **sXLSFile = "s:\brad\book1.xls"**
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & sXLSFile & ";Extended Properties=Excel 8.0;"
        .Open
    End With

    If Not cn Is Nothing Then
        Set rs = New ADODB.Recordset
        With rs
            Set .ActiveConnection = cn
            .Open "**select avg(value) from [sheet1$] where c1='A' and c2='D'**"
            MsgBox "The average is: " & rs(0)
        End With

    End If

    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then
            rs.Close
        End If
    End If
    Set rs = Nothing

    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then
            cn.Close
        End If
    End If
    Set cn = Nothing

End Sub

Hope that helps!

Reference: http://support.microsoft.com/kb/257819