## Explanation

In this example, the goal is to sum the numbers in column C when the item in column B is listed in the range E5:E7. One way to accomplish this is to give the SUMIF function all three values in the named range **things** (E5:E7) as criteria, then use the SUMPRODUCT function to get a total. The formula in G4 is:

```
=SUMPRODUCT(SUMIF(B5:B15,things,C5:C15))
```

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

- For
*range*, we are using B5:B15. These cells contain the values we are testing with multiple criteria. - For
*criteria*, we are using the named range "things" (E5:E7). This range contains the 3 values we are using as criteria. This range can be expanded to include additional criteria as needed. - For
*sum_range*, we are using using C5:C15, which contains numeric values.

Because we are giving SUMIF three separate values for criteria, it will return three results – one result for each value in **things**. The results come back an array as shown below:

```
=SUMIF(B5:B15,things,C5:C15)
=SUMIF(B5:B15,{"apples";"pears";"kiwis"},C5:C15)
={60;42;12} // result from SUMIF
```

This array is returned directly to the SUMPRODUCT function:

```
=SUMPRODUCT({60;42;12})
```

With a single array to process, SUMPRODUCT all items in the array and returns a final result, 114.

### 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:B15,things,0)),C5:C15)
```

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