where things is the named range G5:G7. The result is $19.50, the sum of Price where Item is “Apples”, “Pears”, or “Kiwis”.

SUMIFS with SUMPRODUCT

One way to accomplish this is to give the SUMIFS function all three values in the named range things (G5:G7) as criteria, then use the SUMPRODUCT function to calculate a total. This is the approach in the worksheet shown, where the formula in I5 is: Working from the inside out, the SUMIFS function takes three arguments: sum_range, range, and criteria.

Sum_range is E5:E16 and contains the values we want to sum. Range is B5:B15 and contains the values we are testing. Criteria is the named range things (G5:G7), which contains 3 values.

When Excel evaluates this formula, it retrieves the values in things as an array like this: Because SUMIFS receives 3 separate values for criteria, it returns 3 results in an array like this: The first number is a sum for “apples”, the second number is a sum for “Pears”, and the last number is a sum for “Kiwis”. These results are returned directly to the SUMPRODUCT function like this: With just one array to process, SUMPRODUCT sums the array and returns a final result of $19.50. Note: in the latest version of Excel, you can use the SUM function instead of SUMPRODUCT in this formula. Read more about this topic here: Why SUMPRODUCT?

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.

FILTER function

This problem can also be solved in a more literal way with the FILTER function like this: In this formula, FILTER is configured to use ISNUMBER + MATCH to extract the Price of Items that appear in the named range things. The result is delivered to the SUM function which returns a final result. See this example for more details.

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.