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")

Answer 2

Hi, I want to write a VBA code for "Sumifs" function, for below mentioned data, but don't know how. Can You help me please?

A B C D

App. 1 30 =SUMIFS(C:C,A:A,A2,B:B,B2) Result 80

Man. 2 40 =SUMIFS(C:C,A:A,A3,B:B,B3) Result 40

App. 1 50 =SUMIFS(C:C,A:A,A4,B:B,B4) Result 80

Man. 2 40 =SUMIFS(C:C,A:A,A5,B:B,B5) Result 60

P.S. If possible send answer to my mail address.

Answer 3

Deeno, having a UDF for this is very useful but you could also use plain old =VLOOKUP().

VLOOKUP() only works by looking up one "key" but you can make a concatenated key in a helper column to the left. eg:

W X Y Z    AA
a b 6 ab6  1
a b 7 ab7  2
b b 7 bb7  3

Then =VLOOKUP(A1,$Z$1:$AA$3,2,FALSE) if A1 had the value you are looking for. If your data is more complicated you could join the data with an unused character (eg: a pipe) so you have a|B|6 instead of ab6.