Back to Formula Examples

SUMIFS with Multiple Criteria

Master the powerful SUMIFS function to sum values based on multiple conditions, enabling advanced data analysis and reporting.

Understanding SUMIFS

The SUMIFS function is one of Excel's most powerful tools for conditional summing. Unlike its predecessor SUMIF, SUMIFS allows you to specify multiple criteria across different columns, making it invaluable for complex data analysis.

The function works by examining each row in your data and checking if it meets all specified conditions. If a row satisfies all criteria, its corresponding value is added to the sum. This allows for precise filtering and aggregation of numerical data based on multiple parameters.

Basic Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Parameters:

  • sum_range: The range of cells to sum if the criteria are met
  • criteria_range1: The first range to evaluate against criteria1
  • criteria1: The condition that values in criteria_range1 must satisfy
  • criteria_range2, criteria2, ...: Additional ranges and their corresponding criteria (optional)

Common Use Cases

Sales Data Analysis

Sum sales amounts for a specific product category within a particular date range, or for a specific sales region and customer type.

Financial Reporting

Calculate total expenses for specific departments and expense categories, or sum revenue for particular products during specific time periods.

Inventory Management

Sum quantities of products with specific attributes (e.g., size, color, location) that meet certain threshold criteria.

Project Management

Calculate total hours or costs for tasks assigned to specific team members with particular priority levels or status.

Example Formulas

Basic Two-Criteria Filter

=SUMIFS(D2:D100, B2:B100, "Electronics", C2:C100, "> 500")

Sums values from column D where column B contains "Electronics" AND column C values are greater than 500

Date Range Filtering

=SUMIFS(E2:E100, A2:A100, >= & DATE(2023,1,1), A2:A100, <= & DATE(2023,3,31))

Sums values from column E where dates in column A are between Jan 1 and Mar 31, 2023

Text Contains Criteria

=SUMIFS(F2:F100, C2:C100, "*Premium*", D2:D100, "Complete")

Sums values from column F where column C contains "Premium" and column D is "Complete"

Multiple Numerical Conditions

=SUMIFS(G2:G100, D2:D100, > 100, D2:D100, < 1000, E2:E100, <> 0)

Sums values in column G where column D is between 100 and 1000 AND column E is not equal to 0

Advanced Techniques

Using Cell References in Criteria

Instead of hardcoding criteria values, reference cells to create dynamic formulas that update when inputs change:

=SUMIFS(Sales, Region, $E$2, Category, $E$3, Date, >&$E$4, Date, <=&$E$5)

This formula references criteria values in cells E2, E3, E4, and E5, allowing users to change parameters without modifying the formula.

Combining with Other Functions

SUMIFS can be nested within or combined with other functions for even more powerful calculations:

=SUMIFS(Sales, Date, >&TODAY()-30) / SUMIFS(Sales, Date, >&TODAY()-60, Date, <=&TODAY()-30) - 1

This formula calculates the percentage change in sales between the last 30 days and the previous 30-day period.

OR Logic with Multiple SUMIFS

While SUMIFS uses AND logic (all criteria must be met), you can implement OR logic by using multiple SUMIFS and adding them together:

=SUMIFS(Sales, Region, "North", Category, "Electronics") + SUMIFS(Sales, Region, "South", Category, "Electronics")

This formula sums sales of electronics in either the North OR South regions.

Interactive Example

This is a placeholder for an interactive SUMIFS example.

Future implementation will include an interactive data table that lets you experiment with different SUMIFS criteria and immediately see the results.

Tips and Best Practices

  • Order of Arguments: Always start with the sum_range followed by pairs of criteria_range and criteria. This order is different from SUMIF, where the criteria_range comes first.
  • Criteria Formatting: Use quotation marks around text criteria and comparison operators. For number comparisons, use the >, <, >=, <=, or <> operators within quotation marks.
  • Wildcard Characters: Use asterisk (*) to match any sequence of characters and question mark (?) to match any single character in text criteria.
  • Date Criteria: When working with dates, use DATE functions or cell references to avoid formatting issues.
  • Range Sizes: Ensure all criteria ranges are the same size and shape as the sum_range to avoid calculation errors.
  • Error Handling: Use IFERROR with SUMIFS to handle potential errors, especially when dealing with dynamic criteria.

How ExcelGPT Can Help

ExcelGPT supercharges your work with SUMIFS in several ways:

  • Generate complex SUMIFS formulas from plain language descriptions
  • Help troubleshoot and fix errors in existing SUMIFS formulas
  • Convert multiple SUMIF formulas into more efficient SUMIFS
  • Create advanced combinations of SUMIFS with other functions based on your business needs
  • Build comprehensive dashboards that leverage SUMIFS for dynamic filtering and reporting
  • Suggest optimization techniques for large-scale SUMIFS operations to improve spreadsheet performance

Related Formulas

SUMIF

A simpler version that allows only one criterion. Use when you need to sum based on a single condition.

COUNTIFS

Similar to SUMIFS but counts the number of cells that meet multiple criteria instead of summing values.

AVERAGEIFS

Calculates the average of values that meet multiple criteria, using the same logical structure as SUMIFS.

    v1.0.0