VBA using SUMIFS with more criteria

Question

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"

Mathieu Guindon · Accepted Answer · 2019-09-18 19:31:53Z

2

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

edited Sep 18, 2019 at 19:31

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Sep 18, 2019 at 18:38

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mathieu Guindon Over a year ago

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.

Scott Craner Over a year ago

@MathieuGuindon added.

Collectives™ on Stack Overflow

VBA using SUMIFS with more criteria

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related