Sum if equal to one of many things
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:
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:
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:
This is a more flexible formula in cases where logical conditions become more complex. This formula shows one example.