Back to Topics

SUMIFS

Intermediate
10 min

What is SUMIFS?

SUMIFS adds values that meet multiple conditions, allowing you to sum data based on one or more criteria.

Key Components:

  • Sums values meeting multiple conditions
  • Can use up to 127 condition pairs
  • Works with numbers, dates, and text
  • More flexible than SUMIF

Why it matters

Regional Sales

Sum sales by region AND product

Date Ranges

Calculate totals for specific periods

Multi-condition

Complex business logic

Dynamic Reports

Create interactive summaries

Key Concepts

Sum Range

Range to sum

Example: Sales column...

Criteria Range

Range to evaluate

Example: Product column...

Criteria

The condition to meet

Example: ="Product A"...

Multiple Conditions

Add up to 127 pairs

Example: Product AND Region...

How to use

1

Type =SUMIFS(

Start the formula

2

Select sum range

Numbers to add

3

Select criteria range 1

First column to check

4

Enter criteria 1

First condition

5

Add more criteria

Repeat for additional conditions

6

Close parentheses

Press Enter

Example

Goal: Sum sales for Product A in North region
=SUMIFS(Sales, Product, "A", Region, "North")
Result: Total sales for Product A in North region

Pro Tips

  • •Use wildcards: * and ? for partial matches
  • •Use cell references: Dynamic criteria
  • •Works with dates: Use >, < operators

Practice

Calculate total sales for Q1 2024 for Electronics category
PreviousNext: Index Match