Learn Excel
EN
EnglishENNederlandsNL

Home › Excel functions › SUMIFS

SUMIFS in Excel: Sum with Multiple Criteria

SUMIFS adds up the values that meet all of your conditions: sales in the North region in January, hours per employee above a threshold, costs between two dates. It's the multi-condition version of SUMIF.

SUMIFS · 2 min read
In this guide
  1. SUMIFS syntax
  2. Basic example
  3. Between two dates
  4. Criteria types
  5. OR conditions
  6. Common mistakes
  7. FAQ

SUMIFS syntax

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

The range to add up comes first, followed by pairs of a range and its condition. You can add up to 127 pairs. All ranges must be the same size.

Basic example

ABC
1DateRegionSales
2Jan 8, 2026North1,200
3Jan 15, 2026South800
4Feb 3, 2026North950
5Feb 20, 2026East450
6Mar 2, 2026South700
=SUMIFS(C2:C6, B2:B6, "North", A2:A6, "<"&DATE(2026,2,1))

North sales before February: only row 2 matches both conditions, so the result is 1,200.

Sum between two dates

Use the date column twice, once with >= and once with <=. With the start date in F1 and the end date in F2:

=SUMIFS(C2:C6, A2:A6, ">="&F1, A2:A6, "<="&F2)

From Jan 1 to Feb 28, 2026 this returns 3,400. Always join the operator and the cell with &; writing ">=F1" inside the quotes doesn't work. For a whole month, EOMONTH gives you the last day: "<="&EOMONTH(F1, 0).

Criteria you can use

ConditionCriteria
Equal to text"South"
Not equal"<>South"
Greater than a number">750"
Greater than a cell">"&F3
Starts with"Nor*" (see wildcards)
Not empty"<>"
Empty""

Example: South sales above 750: =SUMIFS(C2:C6, B2:B6, "South", C2:C6, ">750") returns 800.

OR conditions

All conditions in SUMIFS must be true at the same time. For "North or East", add two SUMIFS:

=SUMIFS(C2:C6, B2:B6, "North") + SUMIFS(C2:C6, B2:B6, "East")

That returns 2,600. A shorter version with an array constant: =SUM(SUMIFS(C2:C6, B2:B6, {"North","East"})).

Common mistakes

  • #VALUE!: the ranges have different sizes.
  • Result is 0: dates or numbers are stored as text, or the criteria have a typo or extra spaces.
  • Argument order mixed up: in SUMIFS the sum range is first; in SUMIF it's last.

Frequently asked questions

What's the difference between SUMIF and SUMIFS?

SUMIF takes one condition, SUMIFS one or more. In SUMIFS the range to add up comes first.

Can SUMIFS use the same column twice?

Yes. That's exactly how you sum between two dates or two numbers.

Can SUMIFS sum across multiple columns?

No, the sum range must be one column (or row). Use one SUMIFS per column and add them up, or SUMPRODUCT for more complex cases.