SUMIF and SUMIFS functions in Google Sheets

SUMIF adds the cells that meet one condition; SUMIFS adds the cells that meet several.

These two answer "what is the total for this?": sales for a region, spending for a category, hours for a person. They are the quickest route from a list to a summary.

Syntax

=SUMIF(range, criterion, [sum_range])

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)

Argument	What it is
`range`	The cells to test.
`criterion`	The condition: "North", ">1000", or a cell.
`sum_range`	The cells to add. Last in SUMIF, first in SUMIFS.

Examples

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

=SUMIF(C2:C13,"North",F2:F13)
Result: 8750
Amounts where the region is North.
=SUMIF(F2:F13,">1000")
Result: 15600
No third argument: the amounts that are themselves above 1000.
=SUMIF(D2:D13,"*book*",F2:F13)
Result: 6000
A wildcard: products containing "book".
=SUMIFS(F2:F13,C2:C13,"North",B2:B13,"Asha")
Result: 3450
Two conditions: North and Asha.
=SUMIFS(F2:F13,A2:A13,">="&DATE(2026,2,1),A2:A13,"<"&DATE(2026,3,1))
Result: 2700
A month: from 1 February up to, not including, 1 March.
=ARRAYFORMULA(SUMIF(C2:C13,{"North";"South";"East"},F2:F13))
Result:
8750
5750
4200
In ARRAYFORMULA, SUMIF returns a total for each item of a list.

SUMIF

SUMIF tests each cell of the first range. For each one that passes, it adds the cell on the same row of the sum range.

SUMIFS

SUMIFS allows several conditions, and a row is added only when it passes all of them. Note the order: the range to add comes first, then the pairs. Many people use SUMIFS even for a single condition, to have one order to remember.

Writing conditions

Condition	Meaning
`"North"`	equal to the text
`1000`	equal to the number
`">1000"`	greater than
`"<>Pen"`	not equal to
`H1`	equal to the value in H1
`">="&H1`	at least the value in H1
`"book"`	contains
`"A*"`	starts with A

A comparison sign goes inside the quotation marks. A cell reference stays outside and is joined with &. Text matching ignores case.

For dates, build the date with DATE or point to a cell.

A total for every item, from one formula

Here Sheets differs from older Excel in a useful way. Inside ARRAYFORMULA, SUMIF accepts a list of criteria and returns a total for each, as the last example shows. With the regions listed in H2:H4:

=ARRAYFORMULA(SUMIF(C2:C, H2:H4, F2:F))

Pair it with =UNIQUE(C2:C) in H2 and you have a summary that needs no copying down.

SUMIFS does not expand in this way. For totals on two or more conditions down a column, the usual answers are QUERY with group by, or a SUMIF on a helper column that joins the two keys.

QUERY and FILTER as alternatives

=QUERY(A1:F, "select C, sum(F) where C is not null group by C", 1) produces the whole summary, headings included, and grows with the data.
=SUM(FILTER(F2:F13, C2:C13="North", B2:B13="Asha")) is a readable way to add with conditions, and its conditions can be any formula.

A pivot table, under Insert, does the same without formulas.

Open-ended ranges

Sheets lets a range run to the bottom of the sheet: C2:C. Summaries then include new rows without anyone editing the formula. All the ranges in one formula must still be the same length, so use C2:C with F2:F, not with F2:F13.

Common mistakes

Sum range last in SUMIFS, as in SUMIF.
Ranges of different sizes.
">H1" instead of ">"&H1.
Expecting "or" between SUMIFS conditions. They are always "and". Add two formulas for "or".
Amounts stored as text, which add up to zero.

In Excel

SUMIF and SUMIFS have the same arguments in Excel.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

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