Summing Values by Multiple Criteria in Excel: SUMIFS and SUMPRODUCT Techniques

Excel's SUMIFS function is a powerful tool for summing values based on specified criteria. However, to include multiple criteria or multiple word values, you need to use additional functions such as IF, SUM, or SUMPRODUCT. This article delves into these techniques and provides you with step-by-step guidance to efficiently sum values based on your specific needs.

Introduction to SUMIFS and SUMPRODUCT

SUMIFS is an Excel function designed to sum values based on multiple criteria. However, it doesn't directly support multiple values or words in a single criterion. To overcome this, you can use array formulas with IF or the more versatile SUMPRODUCT function. This article will show you how to use both methods effectively.

Method 1: Using SUM with IF

When to Use SUM with IF

The SUM and IF functions can be combined in an array formula to sum values based on multiple criteria. This method is useful for older versions of Excel that do not support complex array formulas.

Example: Summing Sales for Specific Categories

Assume you have a dataset with Column A representing Categories and Column B representing Values to sum (e.g., sales amounts).You want to sum the sales for specific categories, such as "Electronics" and "Clothing."To achieve this, use the following formula in a cell:

SUM(IF(A2:A10{"Electronics", "Clothing"}, B2:B10))

If using an older version of Excel before Excel 365, press Ctrl Shift Enter to create an array formula will return the sum of values in column B where the corresponding categories in column A are either "Electronics" or "Clothing."

Method 2: Using SUMPRODUCT for a More Streamlined Approach

Why SUMPRODUCT is Preferred

SUMPRODUCT is a more straightforward method as it doesn't require you to enter an array formula. It can handle dynamic criteria without the need for manually creating an array.

Example: Summing Sales Based on Multiple Categories

Assume the same dataset as before with Column A and Column B as categories and sales amounts, sum the sales for the categories "Electronics" and "Clothing" using SUMPRODUCT, use the following formula:

SUMPRODUCT((A2:A10{"Electronics", "Clothing"})*B2:B10)

How SUMPRODUCT Works

The SUMPRODUCT function multiplies the elements in the first array by the corresponding elements in the other array. If the condition in the first array is true, the corresponding values in the second array contribute to the sum. If false, they are ignored.

Handling Alternative Criteria and Partial Matches

Summing Values Based on Alternative Criteria

When dealing with alternative criteria or partial matches, you can use the combination of ISNUMBER and SEARCH functions with SUMPRODUCT. This ensures that the criteria are met accurately.

To test for column A containing either "a" or "c" as part or all of the cell contents, use:

SUMPRODUCT(B3:B16, ISNUMBER(SEARCH({"a", "c"}, A3:A16)))

Handling Cells with Multiple Matches

If a cell might satisfy two or more alternatives, the sum might be too high. In such cases, use SUMPRODUCT to ensure only the correct cells are included:

For example:

SUMPRODUCT((A3:A16{"a", "c"}) * B3:B16)

Summing with SUMIFS and ISNUMBER and SEARCH

If you want to sum values based on multiple word criteria using SUMIFS, you can create an array constant within the function.

For instance:

SUM(SUMIFS(B3:B16, A3:A16, {"a", "c"}))

However, to handle partial matches, use:

SUMPRODUCT(B3:B16, ISNUMBER(SEARCH({"a", "c"}, A3:A16)))

Conclusion

By understanding and utilizing the techniques described in this article, you can efficiently sum values based on multiple criteria in Excel using SUMIFS, SUM, IF, and SUMPRODUCT. Whether you prefer the simplicity of SUMPRODUCT or the array formula approach, these methods provide powerful tools for data analysis in Excel.

For a more visual understanding, refer to my YouTube video at 8:30, where I demonstrate how to add multiple word values and use SUMIFS with alternative criteria.