Sumy with Array Conditional

Question

I am surprised that I can not find it anywhere, which is why I think I should be wrong. I want to be able to include a series of values ​​in sumifs, so it runs as a loop for each value in the conditional (without the need to write "+ sumifs (....) for each value. Here is an example of what I don't work for now

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

Therefore, I am trying to convey all the values ​​in the regions. T: T as a criterion.
For example, Store # 150 Los Angeles and Store # 155 San Diego should go through an argument. Currently, the formula returns the first element that matches it and does not move on to the next element.
I hope this makes sense. Please ask if you need extra clarity.

Answer 1

I think the easiest way is to run the “intermediate” column next to the T column, make sumifs for each row of this column, and then sum this column into another cell. Tables or even just sums of arrays can also be useful here.

I came up with the following in VBA, but I can't fully test it:

 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 am sure that there is a way to make this cycle with the formula, but, having been around, it was not obvious to me.