SumIfs, looking to match part of a string in criteria range with criteria driven from another cell

Question

=SUMIFS('Unspecified Clean URL - Freefor'!B:B,'Unspecified Clean URL - Freefor'!A:A,"*Orlando*")

I can manually type in the City Name I am using as criteria, but I have a column of them and was hoping to use that to feed the criteria for all 100 cities, but I am not sure how to get the value in A2 to come through rather than just the cell range I am using since it is in parentheses.

Can someone tell me if/how this is possible?

=SUMIFS('Unspecified Clean URL - Freefor'!B:B,'Unspecified Clean URL - Freefor'!A:A,"*A2*")

Answer 1

Use an array formula, like this:

=arrayformula(sumif(Freefor!A2:A, A2:A101, Freefor!B2:B))

If you need to match substrings, replace A2:A101 with "*" & A2:A101 & "*".