Archive of Mr Excel Message Board

You seem to be asking for a multiconditional average, that is, average the cells of C if the corresponding cells in A are equal to 1 and those in B are equal to 4.

If this understanding is correct, add the following UDF to your workbook.
[ To do so, activate Tools|Macro|Visual Basic Editor, then Insert|Module, paste the code in the window with title ... (Code), and leave the editor by activating File|Close and Return to Microsoft Excel. ]

Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function

The formula to use for averaging column C is:

=SUMPRODUCT((Used(A:A)=E1)*(Used(B:B)=E2),(Used(C:C)))/MAX(1,SUMPRODUCT((Used(A:A)=E1)*(Used(B:B)=E2)))

where E1 houses 1, the condition value that must holds for column A and E2 4, the condition value for column B.

Note. This formula can also be written up as

=SUMPRODUCT((Used(A:A)=E1)+0,(Used(B:B)=E2)+0,(Used(C:C)))/MAX(1,SUMPRODUCT((Used(A:A)=E1)+0,(Used(B:B)=E4)+0))

if you want to comply exactly with the SUMPRODUCT syntax.