SUMIFS Generator

Multi-Condition Mode

Add as many conditions as you need to filter the sum range.

1. Define Ranges & Criteria

Unlike SUMIF, the sum range always comes first in SUMIFS.

1

2. Visual Helper

#
Date
Region
Sales
...
1
2026-03-01
North
$500
2
2026-03-02
South
$300
3
2026-03-05
North
$850
Sum Range (Result)
Criteria Ranges (Filters)

3. Copy Formula

=SUMIFS(sum_range, criteria_range1, criteria1)

Master SUMIFS: Sum with Multiple Conditions

The SUMIFS function is an enhanced version of SUMIF that allows you to apply multiple criteria. It's essential for financial analysis, sales commission reports, and complex data aggregation.

SUMIF vs SUMIFS: Which should I use?

Many users struggle to choose between SUMIF and SUMIFS. Our recommendation is simple: Learn and always use SUMIFS. Why? SUMIFS can do everything SUMIF can (even just one condition), but its formula structure is entirely different and often easier to read because the result column comes first.

FeatureSUMIFSUMIFS (Winner 🏆)
Conditions SupportMax 1 conditionUp to 127 conditions
Syntax Argument OrderRange, Criteria, Sum_RangeSum_Range, Criteria_Range1, Criteria1...

Real-World Examples

  • HR & Payroll: Sum total hours worked by a specific employee (Condition 1) between January 1 and January 31 (Conditions 2 & 3).
  • E-commerce: Sum total revenue for "Electronics" (Condition 1) where order status is "Delivered" (Condition 2) and discount was ">0" (Condition 3).

Pro Tip: Ensure that your sum_range and all criteria_range arrays are exactly the same size (e.g., all span rows 2 to 100). If they are mismatched, Excel will return an error.

Common Errors & Fixes

  • SUMIFS returns 0 or wrong total

    Causes:
    • Sum range and criteria ranges have different heights or columns.
    • Criteria in wrong order (text without quotes, date not as DATE() or cell ref).
    • Using SUMIF-style argument order (sum range must come first in SUMIFS).
    Fixes:
    • Use same-sized ranges: e.g. A1:A100, B1:B100, C1:C100.
    • Put text in quotes; use ">="&A1 for dates where A1 is a date.
    • Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2).

Frequently Asked Questions

What is the correct SUMIFS syntax order (sum_range criteria_range1 criteria1)?

The syntax is exactly: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...). The sum_range MUST come first, followed by pairs of criteria ranges and their specific conditions.

Why does SUMIFS return 0?

This usually happens if criteria_range and sum_range are different sizes, or text criteria are missing quotes (like ">100"). Ensure all ranges have the exact same number of rows.

How do I use SUMIFS with dates?

To sum between two dates, use two criteria on the same date column: criteria_range1 with ">="&start_date, and criteria_range2 with "<="&end_date.

SUMIF vs SUMIFS: What is the difference?

SUMIF is for a single condition and its syntax is (range, criteria, sum_range). SUMIFS handles multiple conditions and its syntax is (sum_range, criteria_range1, criteria1...). We recommend always using SUMIFS because it is more flexible.

Can SUMIFS use wildcards?

Yes. Use * for any characters and ? for one character: =SUMIFS(C:C, A:A, "*North*", B:B, ">100").

Related Formulas

SUMIFCOUNTIFPMTCOUNTIFSAVERAGEIF

Want to become an Excel Pro?

Stop searching for formulas. Master Excel in 30 days with this top-rated course.

Learn More