Summary

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.

Generic formula

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

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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.