# Multiple AverageIf in Excel

I have an Excel data sheet with lots and lots of rows that looks something like

C1    C2    Value
---   ---   -----
A     C    0.228
B     D    0.234
A     D    0.359
A     C    0.125
...

I want to find the average of Value where C1 = A and C2 = D , what's the best way to go about doing this? I know about the AVERAGEIF and COUNTIF and SUMIF functions, but they don't seem to work with multiple conditions...

-
I would suggest providing a formula you have tried and maybe some one can point out where the problem lies. –  Darius Aug 25 '11 at 0:28

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:

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

' set the location of the Excel worksheet
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
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
rs.Close
End If
End If
Set rs = Nothing

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

End Sub

Hope that helps!

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

-
This is why more schools should teach Basic. Everyone friggin uses Office. . . –  surfasb Aug 25 '11 at 1:34
@surfasb And everyone friggin uses Office on Windows in an environment that doesn't think that macros are dangerous ;-) –  digitxp Aug 25 '11 at 1:35
@digitxp - lol! but you're right. :) –  bradzo Aug 25 '11 at 2:21