Excel Formula, Sumifs, condition is an array range

Question

Goal: Use SUMIFS to get the sum of value if color is Red or Yellow. Outcome should be 3.

+---+--------+-------+---+-----------+
|   |   A    |   B   | C |     D     |
+---+--------+-------+---+-----------+
| 1 | Key    | Value |   | Condition |
| 2 | Red    | 1     |   | Red       |
| 3 | Yellow | 2     |   | Yellow    |
| 4 | Green  | 3     |   |           |
+---+--------+-------+---+-----------+

Problem:

• It works if I hardcode the condition {"Red","Yellow"}. The result is 3.

  =SUM(SUMIFS(B2:B4, A2:A4, {"Red","Yellow"}))

• But if I reference the condition by cell D2:D3, I get 0.

  =SUM(SUMIFS(B2:B4, A2:A4, D2:D3))

Question: How do I reference the condition dynamically by cell and make it work?

Answer 1

Use SUMPRODUCT() instead of SUM():

=SUMPRODUCT(SUMIFS(B2:B4,A2:A4,D2:D3))

---

One note:

This variation allows the expansion of the lists without the need to reapply the ranges:

=SUMPRODUCT(SUMIFS(B:B,A:A,D2:INDEX(D:D,MATCH("zzz",D:D))))