SUMIFS function in Excel

SUMIFS adds up the cells that meet several conditions at once.

SUMIFS is SUMIF with room for more conditions. A row is included only when it passes every condition, so the conditions are joined by "and". It is the workhorse of monthly reports: sales for one region in one month, expenses for one head in one quarter.

Syntax

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

Argument	What it is
`sum_range`	The cells to add. Note that it comes first here, unlike in SUMIF.
`criteria_range1`	The first range to test.
`criteria1`	The condition for the first range.
`criteria_range2, criteria2, ...`	More pairs of range and condition. A row is added only if it passes all of them.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=SUMIFS(F2:F13,C2:C13,"North",B2:B13,"Asha")
Result: 3450
Amounts where the region is North and the salesperson is Asha.
=SUMIFS(F2:F13,C2:C13,"North",D2:D13,"Bag")
Result: 6750
Bags sold in the North.
=SUMIFS(F2:F13,A2:A13,">="&DATE(2026,2,1),A2:A13,"<"&DATE(2026,3,1))
Result: 2700
A whole month: from 1 February up to, but not including, 1 March.
=SUMIFS(F2:F13,E2:E13,">=10",E2:E13,"<=40")
Result: 6300
Two conditions on the same column: units between 10 and 40.
=SUMIFS(E2:E13,D2:D13,"Pen",F2:F13,">500")
Result: 140
Units of Pen in sales worth more than 500.
=SUMIFS(F2:F13,C2:C13,"West")
Result: 0
A condition that nothing meets gives 0, not an error.

The order of the arguments

In SUMIF the range to add comes last. In SUMIFS it comes first. The pairs of "where to look" and "what to look for" follow. This difference is the most common reason a SUMIF that worked gives nonsense when a condition is added.

Many people use SUMIFS all the time, even for a single condition, so that there is only one order to remember.

Conditions

Each condition is written exactly as in SUMIF: text in quotation marks, comparison signs inside the quotation marks, cell references joined on with &, wildcards * and ? for part of a text.

All the ranges must be the same size and shape. If one is a row longer than the others, the result is #VALUE!.

A month, a quarter, a year

Dates are the main reason to use two conditions on the same column. The dependable pattern is "on or after the first day, and before the first day of the next period":

=SUMIFS(amounts, dates, ">="&start, dates, "<"&next_start)

With the start date in a cell, EDATE gives the next month: "<"&EDATE(H1,1). Writing it this way is correct even if the dates carry a time of day, which "<="&last_day is not.

A summary table

Put regions down the side and products across the top, and one formula fills the grid:

=SUMIFS($F$2:$F$13,$C$2:$C$13,$H2,$D$2:$D$13,I$1)

The data ranges are fully fixed. $H2 fixes the column so that the formula always reads the region from column H. I$1 fixes the row so that it always reads the product from row 1. The guide on cell references explains this mixed style.

Either of two values

Conditions in SUMIFS are always "and". For "North or East", add two SUMIFS together, or give a list in curly brackets and wrap the whole thing in SUM: =SUM(SUMIFS(F2:F13,C2:C13,{"North","East"})).

When SUMIFS is not enough

SUMIFS needs real ranges and simple conditions. It cannot test "the month of the date is 3" or "the first letter is A" without a helper column. For that, SUMPRODUCT or FILTER inside SUM is more flexible.

Common mistakes

The sum range placed last, as in SUMIF.
Ranges of unequal size.
A comparison written as ">=H1", which should be ">="&H1.
Expecting "or" between conditions.

In Google Sheets

SUMIFS is the same in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.