Excel Function – Sumifcr – Multiple Criteria

Excel Function – SUMIF Next version – sumifcr with multiple criteria

Public Function sumifcr(a As Range, b As Range, c As String) As Double

'a = criteria range TEXT
'b = sum range NUMBER
'c = criteria comma seperated TEXT


Dim aa As Variant
Dim bb As Variant
Dim cc As Variant
Dim ee As Double
Dim dd As Long
Dim ff As Long

aa = a
bb = b

ee = 0
cc = Split(c, ",")

dd = UBound(aa, 1) - LBound(aa, 1) + 1
ff = UBound(cc, 1) - LBound(cc, 1)

For i = 1 To dd
    For j = 0 To ff

        'If (aa(i, 1) = CLng(cc(j))) Then
    
        If (aa(i, 1) = cc(j)) Then
            ee = ee + bb(i, 1)
                
        End If
        
    Next j
Next i

sumifcr = ee
Debug.Print ee


End Function

Sub aaaa()
MsgBox sumifcr(Range("G5:G16"), Range("H5:H16"), Range("J5").Value)

End Sub

With this function we can sum with multiple criteria in single cell.

It has three variable,

‘a = criteria range TEXT format

‘b = sum range NUMBER format

‘c = criteria, comma separated TEXT format

Copy this function use it anywhere in excel file.