CODEWITHSUNDEEP
excelworksheet-function
Zombian
Zombian

Reputation: 1435

Sumifs with Array conditional

I'm surprised I can't find this anywhere which makes me think I must be going about this incorrectly. I want to be able to include a series of values within sumifs so it performs like a loop for each value in the conditional (without having to write a " +sumifs(....) for each value. Here's an example of what I have so far that is not working 

`=SUMIFS(Sum,Range1,Criteria1, '[Stores.xlsx]Sheet1'!$H:$H, "Store #"&Regions!$T:$T&"*")`

So I'm trying to pass every value in Regions!T:T as a criteria.
For example "Store #150 Los Angeles" and "Store #155 San Diego" would both need to be passed through the argument. Currently the formula just returns the first item it matches and doesn't continue to the next item.
I hope that makes sense. Please ask if you need more clarity.

Upvotes: 4

Views: 2364

Answers (1)

jonsca
jonsca

Reputation: 10381

I think the easiest is to start an "intermediate" column next to the T column, do a sumifs for each of the rows of that column, and then sum that column into another cell. Tables or even just array sums may also be helpful here.

I came up with the following in VBA, but I cannot test it completely:

Option Explicit
Function SumSumIfs(ByVal inp As Range) As Integer

    Dim i As Integer
    Dim QBData As Worksheet
    Dim Stores As Worksheet

    Set QBData = Workbooks.Open("QBData.xlsx").Sheets("Sheet1")
    Set Stores = Workbooks.Open("Stores.xlsx").Sheets("Sheet1")

    Dim QBRange1, QBRange2, SalesRange As Range

    Set QBRange1 = QBData.Range("H1:H" & Range("H1").End(xlDown).Row)
    Set QBRange2 = QBData.Range("I1:I" & Range("I1").End(xlDown).Row)
    Set SalesRange = QBData.Range("H1:H" & QBData.Range("H1").End(xlDown).Row)


    For i = 1 To inp.End(xlDown).Row
      SumSumIfs = SumSumIfs + Application.WorksheetFunction.SumIfs(QBRange1, QBRange2, _
                  "=" & Stores.Cells(16, 5), StoreRange3, "=" & inp.Cells(i, 19))
    Next i

End Function

Again, I'm certain there's a way to do this looping with the formula, but searching around, it was not evident to me.

Upvotes: 1

Related Questions