SUMIFS Made Easy — Sum with Multiple Conditions (Step by Step) | SheetMaster

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).
  • Finance: Sum all invoices over $5,000 in Q1 2026 that are still "Unpaid".
  • Marketing: Sum total ad spend for campaign "Summer Sale" where ROI > 200%.

SUMIFS with Date Ranges

Sum values between two dates by using two conditions on the same date column:

=SUMIFS(C:C, A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,3,31))

This sums column C for all rows where column A dates fall within Q1 2026.

Common Mistakes and How to Fix Them

  • Wrong argument order: SUMIFS syntax is =SUMIFS(sum_range, criteria_range1, criteria1). The sum range comes FIRST—unlike SUMIF where it comes last.
  • Criteria not quoted: Text criteria need quotes: "=Sales". For cell references: ">"&A1.
  • Mismatched range sizes: All ranges must have the same number of rows or SUMIFS returns wrong results or errors.

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

How to do a SUMIFS with multiple criteria?

Use =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). The sum_range comes first, then each pair of (range, condition). Example: =SUMIFS(C:C, A:A, "North", B:B, ">100") sums column C where A is "North" AND B > 100.

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.

Can SUMIFS use multiple criteria on the same column?

Yes, use SUMIFS with separate criteria pairs on the same range: =SUMIFS(C:C, A:A, ">100", A:A, "<500") sums C where A is between 100 and 500. Or add multiple SUMIFS for OR logic.

Can SUMIFS use wildcards?

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

Related Formulas

SUMIFCOUNTIFSAVERAGEIFIFIFS

Want to become an Excel Pro?

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

Learn More