# Sum if equal to one of many things

=SUMPRODUCT(SUMIF(range,things,values))

To sum values when cells are equal to one of many things, you can use a formula based on the SUMIF and SUMPRODUCT functions. In the example shown, the formula in H5 is:

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

where **things** is the named range E5:E7.

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:

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.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

## Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.