OK, you said you have a 2D array (not an excel range), but the exact shape of the array was not specificated. So I have to assume your 2D array is call "arr" and has the form of: arr(c,r) as variant , where r is used for accessing the rows and c for the columns (1 for "ID1", 2 for "ID2", 3 for "ID3" and 4 for "Value"). (See "note 1" and "note 2" for further clarification if you are not following the idea).
Then you just have to make a small loop:
tot = 0
For i = LBound(arr, 2) To UBound(arr, 2) ' The "2" in the second paramenter is
' for getting the lower and upper bound
' of the "2nd" dimention of the array
If arr(1, i) = A And arr(2, i) = B And arr(3, i) = C Then
tot = tot + arr(4, i)
End If
Next i
The tot variable will have the total you was trying to calculate. Easy??
If you want to warp the previous in a function, you can use:
Public Function SumIfMyArray(arr As Variant, A As Variant, _
B As Variant, C As Variant) As Double
Dim i as Long
Dim tot As Double
tot = 0
For i = LBound(arr, 2) To UBound(arr, 2)
If arr(1, i) = A And arr(2, i) = B And arr(3, i) = C Then
tot = tot + arr(4, i) 'Adding the filtered value
End If
Next i
SumIfMyArray = tot 'Returning the calculated sum
End Function
Use it like: Debug.Print SumIfMyArray(YouArr, 1, 1, 1). Hope this helps.
MORE COMPLEX (BUT FLEXIBLE):
Now, if you want to have a very generic function that support different criterias and at the same time to be flexible with the columns, you can use the code below (Note, I'm using the ParamArray like in other reply). Actually the function can use an array in the form arr(c,r) (that array form is easier to adding more rows with redim instruction) and the second in the form arr(r,c) (this array form is simpler if you copy an excel range using arr=range("A1:D5") ).
Private Function SumIfConditionsMetArray(ColToAdd As Long, Arr As Variant, _
TypeArrayIsRC As Boolean, _
ParamArray Criteria() As Variant) As Double
' Returns: The sum of values from a column where
' the row match the criteria.
' Parameters:
' 1) Arr: An array in the form of arr(row,col) (
' (like the array passed by an excel range)
' 2) ColToAdd: Index of column you want to add.
' 3) TypeArrayIsRC: 'True' if the array passed if in the
' form of arr(Row,Column) or 'False' if
' the array is in the form arr(Column,Row).
' Note that passing an range as
' arr=range("A1:B3").value , then "true"
' should be used!
' 4) Criteria: a list of criteria you want to use for
' filtering, if you want to skip a column
' from the criteria use "Null" in the
' parameter list.
'
' Example: Debug.Print SumIfConditionsMetArray(4, data, true, 9, null, 5)
' (It means: sum column 4 of data where 1st column
' match "9" and 3rd column match "5".
' The 2nd column was skipped because of null)
Dim tot As Double
Dim CountCol As Long
Dim r As Long, c As Long
Dim conditionsMet As Boolean
Dim cExtra As Long
Dim DimRow As Long, DimCol As Long
If TypeArrayIsRC Then
DimRow = 1: DimCol = 2
Else
DimRow = 2: DimCol = 1
End If
'Some checking...
If ColToAdd < LBound(Arr, DimCol) Or ColToAdd > UBound(Arr, DimCol) Then
Err.Raise vbError + 9, , "Error in function SumIfConditionsMetArray. ColToAdd is out of the range."
End If
'Correction in case of different array bases..
cExtra = LBound(Arr, DimCol) - LBound(Criteria) 'In case the lower bound were different...
'Limit the last column to check
CountCol = UBound(Criteria)
If CountCol > UBound(Arr, DimCol) - cExtra Then
'Not raising an error, just skip out the extra parameters!
'(Put err.raise if you want an error instead)
CountCol = UBound(Arr, DimCol) - cExtra
End If
On Error GoTo errInFunction
'''' LOOP ''''
Dim A As Long
Dim B As Long
tot = 0
For r = LBound(Arr, DimRow) To UBound(Arr, DimRow)
If TypeArrayIsRC Then
A = r
Else
B = r
End If
conditionsMet = True
For c = LBound(Criteria) To CountCol
If Not IsNull(Criteria(c)) Then
If TypeArrayIsRC Then
B = c + cExtra
Else
A = c + cExtra
End If
If Arr(A, B) <> Criteria(c) Then
conditionsMet = False 'Creteria not met
End If
End If
Next c
If TypeArrayIsRC Then
B = ColToAdd
Else
A = ColToAdd
End If
If conditionsMet Then
tot = tot + Arr(A, B) 'Adding the value
End If
Next r
SumIfConditionsMetArray = tot 'Returning the calculated sum
Exit Function
''' END '''
errInFunction:
Err.Raise Err.Number, , "Error in function SumIfConditionsMetArray. Check the parameters are inside the bounds."
End Function
Is a bit more tricky but much more flexible. You can use it with a range as:
Dim MyArr as variant
MyArr = ActiveSheet.range("A1:G10").Value ' Note: use ".Value" at end
' and not start with "Set"
Debug.Print SumIfConditionsMetArray(4, MyArr, True, 100, null, 100)
' This will add the value of the 4th column, were the row
' has 100 in the first column and 100 in the 3rd column.
Hoping this help with your question.
Regards, Andres
** Note 1 ** When having an array in the form of arr(c,r) you can access any element by giving the coordinates inside the parenthesis. For example, if you want to access the value of 4th column of the 2nd row, you have to code arr(4,2) and you will get the value of 5 (provided you are testing the same example of your question. Check it in your first table).
** Note 2 ** I have a reason for the arr(c,r) instead of arr(r,c). The reason is because it is much more easier if you want to add more rows with the redim instruction if you have the row coordinate in a the last position. But if you 2D array is coming from a excel range (Using for example something like arr = range("A3:D6").value), then it will be better to flip the r and c position in the code.
