## Explanation

In this example, the goal is to sum the numbers in column E when the item in column B appears in the range G5:G7. The named range **things** is not required. It is used only for convenience and can be expanded as needed to include additional criteria. The article below explains several ways to solve this problem.

### SUMIFS with SUMPRODUCT

One way to accomplish this is to give the SUMIFS function all three values in the named range **things** (G5:G7) as criteria, then use the SUMPRODUCT function to calculate a total. This is the approach in the worksheet shown, where the formula in I5 is:

```
=SUMPRODUCT(SUMIFS(E5:E16,B5:B16,things))
```

Working from the inside out, the SUMIFS function takes three arguments: *sum_range*, *range*, and *criteria.*

*Sum_range is*E5:E16 and contains the values we want to sum.*Range is*B5:B15 and contains the values we are testing.*Criteria*is the named range**things**(G5:G7), which contains 3 values.

When Excel evaluates this formula, it retrieves the values in **things** as an array like this:

`=SUMPRODUCT(SUMIFS(E5:E16,B5:B16,{"apples";"pears";"kiwis"}))`

Because SUMIFS receives 3 separate values for *criteria*, it returns 3 results in an array like this:

`{3.75;11.25;4.5}`

The first number is a sum for "apples", the second number is a sum for "Pears", and the last number is a sum for "Kiwis". These results are returned directly to the SUMPRODUCT function like this:

`=SUMPRODUCT({3.75;11.25;4.5})`

With just one array to process, SUMPRODUCT sums the array and returns a final result of $19.50.

*Note: in the latest version of Excel, you can use the SUM function instead of SUMPRODUCT in this formula. Read more about this topic here: Why SUMPRODUCT?*

### ISNUMBER and MATCH

The above formula works fine, but has some limitations due to the nature of SUMIF. As an alternative, you can use the formula below, which uses the ISNUMBER function with the MATCH function to achieve the same result:

```
=SUMPRODUCT(--ISNUMBER(MATCH(B5:B16,things,0)),E5:E16)
```

This is a more flexible formula in cases where logical conditions become more complex. This formula shows one example.

### FILTER function

This problem can also be solved in a more literal way with the FILTER function like this:

`=SUM(FILTER(E5:E16,ISNUMBER(MATCH(B5:B16,things,0)),0))`

In this formula, FILTER is configured to use ISNUMBER + MATCH to extract the Price of Items that appear in the named range *things*. The result is delivered to the SUM function which returns a final result. See this example for more details.