Search code examples
vbacriteriasumifs

VBA using SUMIFS with more criteria

I have a problem with using the SUMIFS in VBA. The thing is I need to use SUMIFS to sum 1+ cities in my dataset.

I have tried to google and use STackoverflow and found 2 anwers where I tried both, but none of them workd. One of them was:

VBA - SumIfs with Or

Dim D As Double

D = WorksheetFunction.Sum(WorksheetFunction.SumIfs(Range("D2:D2000"), _
Range("A2:A2000"), Array("Århus", "København")))
MsgBox D

I expect a numeriacal result, but I get the result "Run-time error '13': Type mismatch"

Solution

  • These will need to be late bound so use Application. instead of WorksheetFormula:

    As per @MathieuGuindon:

    The result should be captured into a Variant to prevent a type mismatch upon assignment of D if any of the values involved is a worksheet error.

    Dim D As Variant

D = Application.Sum(Application.SumIfs(Range("D2:D2000"), _
Range("A2:A2000"), Array("Århus", "København")))
If IsNumeric(D) Then MsgBox D