SUMPRODUCT instead of SUMIFS for dynamic conditions
When your criteria column changes on the fly, SUMPRODUCT is the cleaner pattern.
SUMIFS is great until the column you're summing is decided at runtime. Then you start writing nested INDIRECT formulas and everything turns to mud. SUMPRODUCT handles this in one expression.
The setup
You have a transaction table. A user picks a measure (Amount, Quantity, Tax) from a dropdown, and you need to sum that column where Region = "EMEA".
=SUMIFS(C2:C1000, B2:B1000, "EMEA")
The column C2:C1000 is hard-coded. Change the dropdown, change the formula.
The fix
=SUMPRODUCT(
(B2:B1000 = "EMEA") *
INDEX(A2:E1000, 0, MATCH(G1, A1:E1, 0))
)
G1 holds the dropdown value. MATCH finds the right column index. INDEX(..., 0, n) returns the whole column as an array. SUMPRODUCT multiplies the boolean mask by the values.
When not to use it
On tables over 100k rows, SUMPRODUCT is materially slower than SUMIFS because it can't push down the filter. For those cases, build a small wrapper that picks the column with CHOOSE and use SUMIFS inside.