How many criteria can SUMIFS take?

Up to 127 range and criteria pairs.

How do I sum between two dates?

Use two criteria on the date column: ">="&start_date and "<="&end_date.

Does SUMIFS work across sheets?

Yes, but all ranges must be the same size, and closed workbooks are not supported by references to other files.

Whole-column references over huge data can be slow. Use exact ranges or an Excel Table.

← All functions

Math

SUMIFS

Q: Does SUMIFS work across sheets?

Yes, but all ranges must be the same size, and closed workbooks are not supported by references to other files.

Q: Why is SUMIFS slow?

Whole-column references over huge data can be slow. Use exact ranges or an Excel Table.

Sum with several conditions.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, …)

Example: =SUMIFS(C:C,A:A,"Boston",B:B,">100")

Available in: All versions

SUMIFS adds up the cells that meet all of several conditions: =SUMIFS(sum_range, criteria_range1, criteria1, …). Use it for totals such as sales for one region above a size, or expenses for a category within a date range.

When to use it

You need a total filtered by two or more columns (region and product, category and month).
You sum amounts between two dates or two values.
You build summary tables that update as data is added.
You want AND logic across criteria without helper columns.

Arguments explained

sum_range. The cells to add up. Unlike SUMIF, it comes first, so double-check the order when you convert one function to the other.
criteria_range1. The first range that is tested. All criteria ranges must have exactly the same size and shape as sum_range.
criteria1. The condition for criteria_range1: a value, a comparison such as ">=100" or a cell reference.
criteria_range2, criteria2, …. Further pairs, up to 127. A row is included only when all conditions are met (AND logic).

Worked examples

Two conditions

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =SUMIFS(C2:C7,A2:A7,"East",B2:B7,"Ana")

Result: 1810

Sums Amount only where Region is East and Rep is Ana: 1200 + 610.

Exclude a region, with a minimum

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =SUMIFS(C2:C7,A2:A7,"<>North",C2:C7,">=600")

Result: 4160

"<>North" excludes the North rows; ">=600" drops small amounts: 1200 + 850 + 1500 + 610.

Between two values

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =SUMIFS(C2:C7,C2:C7,">500",C2:C7,"<1000")

Result: 2430

The same range can appear twice to build a lower and an upper bound: 850 + 970 + 610.

Try it

A tiny spreadsheet with example data. Edit the formula or any number and see the result.

Formula in F2: =SUMIFS(C2:C7,A2:A7,"East",B2:B7,"Lamp")

Example spreadsheet for SUMIFS
	A	B	C	D	E	F
1	Region	Product	Units	Price		Result
2	East	Pen	120	2.5		15
3	West	Pen	80	2.5
4	East	Book	45	12
5	North	Book	60	12
6	West	Lamp	30	25
7	East	Lamp	15	25

Result in F2: 15

Practice this in a lesson: Multiple Conditions

Common errors and how to fix them

#VALUE!. All criteria ranges must have exactly the same size as the sum range.
Result is 0. Criteria text does not match (extra spaces, different spelling), or the sum column contains text numbers.
Argument order confusion. Unlike SUMIF, the sum range comes first in SUMIFS.
OR logic needed. SUMIFS only does AND. For OR, add several SUMIFS together.

Related functions

Frequently asked questions

How many criteria can SUMIFS take?: Up to 127 range and criteria pairs.
How do I sum between two dates?: Use two criteria on the date column: ">="&start_date and "<="&end_date.
Does SUMIFS work across sheets?: Yes, but all ranges must be the same size, and closed workbooks are not supported by references to other files.
Why is SUMIFS slow?: Whole-column references over huge data can be slow. Use exact ranges or an Excel Table.