Exceljet

Quick, clean, and to the point

Sum if equal to one of many things

Excel formula: Sum if equal to one of many things
Generic formula 
=SUMPRODUCT(SUMIF(range,things,values))
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.

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;30;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.

Author 
Dave Bruns

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.