Search code examples
arraysvbasumifs

vba sumifs multiple criteria in single column

lets say I have in columns A and B this:

A    5
A_1  3
A_2  2
A_3  3
A_4  4

and i want to do:

Application.SumIfs(range("b:b"), range("b:b"), {"A", "A_2"})

however this doesnt work, the array values.

I wish not to go through EVALUATION() (or [] equivalent) or not even do the loop through range. So Im looking for a way to use multiple options on criteria values.

PS: SumIfs is just an example, because I have more conditions than just one, so If someone would suggest SumIf, then no, i have reasons not to use SumIf

Thank you

Solution
    1. the SUMIFS must be Late Bound: Application.SumIfs vs. Application.WorksheetFunction.SumIfs
    2. Wrap in Application.SumProduct
    3. Use Array()
    Application.SumProduct(Application.SumIfs(range("b:b"), range("a:a"), Array("A", "A_2")))