excel - VBA code for SUMIFS?

Question

I'm trying to write a custom function that will let me retrieve a cell from the first row in a range that meets x number of criteria. I imagine this would be very similar to the way SUMIFS works, just simpler in that it doesn't continue processing after the first match.

Does anyone know code to reproduce the SUMIFS (excel 07) function in VBA?

So, for example, if I have a table in excel like:

W X Y Z
a b 6 1
a b 7 2
b b 7 3

I want to be able to write a function that will give me the value in column Z where columns W=a, X=b, Y>=7 (in other words the value 2).

SUMIFS can approximately do this, assuming the record I want is unique and I'm looking to return a number. For my purposes though, those assumptions won't work.

Answer 1

An example using ADO.

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

'I want to be able to write a function that will give me the value '
'in column Z where columns W=a, X=b, Y>=7 '
'(in other words the value 2).' 

strSQL = "SELECT Top 1 Z " _
         & "FROM [Sheet1$] " _
         & "WHERE W='a' And X='b' And Y>=7"

rs.Open strSQL, cn

Result = rs.Fields("Z")