VBA code for SUMIFS?

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.

From stackoverflow

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

  TheDeeno : Rather then write to another sheet can the function return a scalar value so it can be used in a formula?

  Remou : Yes. I have amednded to show this.

  TheDeeno : I should be able to replace [Sheet1$] with a named table range correct?

  Remou : Yes, eg, Select ... From Table1

  TheDeeno : Thanks for all the help Remou. One last question: do you know if the SUMIFS function leverages oledb like this? Or is it using a more efficient mechanism?

  Remou : SumIf is a built-in function and uses whatever Microsoft used to write Excel :). You can use built-in functions in VBA and there are undoubtedly a number of ways of doing what you want, but I find ADO very useful when there are several criteria and a set of records.

• IMHO ADO is not suitable for use in excel worksheet functions (poor performance and cannot easily be used on the worksheet containing the data). here is a VBA alternative:

  
  Function MFind(theRange As Range, ParamArray Tests() As Variant) As Variant
  '
  ' Parameters are:
  ' The Range to be searched
  ' the values to be searched for in successive columns
  ' all search values except the last use =
  ' the last search value uses >=
  ' the function returns the value from the last column in the range
  '
      Dim vArr As Variant
      Dim j As Long
      Dim k As Long
      Dim nParams As Long
      Dim blFound As Boolean
  
  vArr = theRange.Value2
  nParams = UBound(Tests) - LBound(Tests) + 1
  If nParams >= UBound(vArr, 2) Then
      MFind = CVErr(xlErrValue)
      Exit Function
  End If
  
  For j = 1 To UBound(vArr)
      blFound = True
      For k = LBound(Tests) To nParams - 2
          If vArr(j, k + 1) <> Tests(k) Then
              blFound = False
              Exit For
          End If
      Next k
      If blFound Then
          If vArr(j, nParams) >= Tests(nParams - 1) Then
              MFind = vArr(j, UBound(vArr, 2))
              Exit For
          End If
      End If
  Next j
  
  
  End Function
  

• 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.

  TheDeeno : You're better off asking a new question for this. If this question is related, link to it in your question body. You'll dramatically decrease the visibility of your question by asking this way.

• 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.