Hi there,
Here is a neat function to sum data using multiple criteria. In
addition, it also groups and sums values based on your need.



Option Compare Text

Function XSumIf(myRange As Range, All, ParamArray vals())

'XSumIf groups and sums data, in the last column, based on n number of
conditions.
'The user may replace a criteria with a text to indicate to the
function to group values
'through the "All" argument. Let's go directly to an example:

'Car Yellow US 15
'Truck Yellow US 10
'Truck Red France 8
'Bike Yellow US 6

'=XSumif(A1:D4,"All", "Car", "Yellow", "US") === 15
'=XSumif(A1:D4,"All", "All", "Yellow", "US") === 31
'=XSumif(A1:D4,"*", "Truck", "*", "*") === 18

'SQL users! The function works similar to a "group by query".


Dim i, j, r, c As Long
Dim vArray As Variant
Sum1 = 0
Sum2 = 0
r = myRange.Rows.Count
c = myRange.Columns.Count
vArray = Range(myRange.Address).Value

For i = 1 To r

For j = LBound(vals, 1) To UBound(vals, 1)
If vals(j) = All Then
Sum2 = vArray(i, c)
ElseIf vals(j) = vArray(i, j + 1) Then
Sum2 = vArray(i, c)
Else
Sum2 = 0
Exit For
End If
Next j

Sum1 = Sum1 + Sum2

Next i

XSumIf = Sum1

End Function

' by Samy Zeraouli