What is the difference between SUMIF and SUMIFS?

SUMIF has one condition and puts the sum range last. SUMIFS accepts many conditions and puts the sum range first.

Can SUMIF use OR logic?

Add two SUMIFs, or wrap criteria in an array constant: SUM(SUMIF(A:A,{"East","West"},C:C)).

How do I sum by date with SUMIF?

Use a comparison with a cell reference, for example ">="&E1, where E1 holds the start date.

← All functions

Math

SUMIF

Sum with one condition.

Syntax

SUMIF(range, criteria, [sum_range])

Example: =SUMIF(A2:A20,"Boston",C2:C20)

Available in: All versions

SUMIF adds up the cells in a range that meet one condition: =SUMIF(range, criteria, [sum_range]). Use it to total sales for one region, expenses for one category or any amounts that match a single test.

When to use it

You need a subtotal for a single category, rep or month.
You want to sum numbers above or below a threshold.
The data is laid out in one table and you do not want to filter it first.
You have two or more conditions: switch to SUMIFS.

Arguments explained

range. The cells that are tested against the criteria, for example a column of regions.
criteria. The condition: a value such as "East", an expression in quotes such as ">100" or "<>0", or a cell reference. Wildcards ? and * work in text criteria.
sum_range. Optional. The cells that are added up. It must be the same size as range. If omitted, the cells in range itself are summed.

Worked examples

Total for one region

	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: =SUMIF(A2:A7,"East",C2:C7)

Result: 2240

Adds the Amount (C) of every row where Region (A) is East: 1200 + 430 + 610.

Amounts over a threshold

	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: =SUMIF(C2:C7,">900")

Result: 3670

With one range, the same cells are tested and summed: 1200 + 970 + 1500.

Wildcard on names

	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: =SUMIF(B2:B7,"A*",C2:C7)

Result: 1810

"A*" matches names starting with A, here Ana twice: 1200 + 610.

Try it

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

Formula in F2: =SUMIF(A2:A7,"East",C2:C7)

Example spreadsheet for SUMIF
	A	B	C	D	E	F
1	Region	Product	Units	Price		Result
2	East	Pen	120	2.5		180
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: 180

Practice this in a lesson: COUNTIF and SUMIF

Common errors and how to fix them

Result is 0. Numbers stored as text are not summed, and criteria with extra spaces do not match. Check that the amounts are real numbers.
Operators must be in quotes. Write ">900", not >900. To use a cell, concatenate: ">"&E1.
Misaligned ranges. range and sum_range should be the same size. Different sizes give shifted, misleading totals.
Ranges move when copied. Lock the ranges ($A$2:$A$7) before filling formulas down or across.

Related functions

Frequently asked questions

What is the difference between SUMIF and SUMIFS?: SUMIF has one condition and puts the sum range last. SUMIFS accepts many conditions and puts the sum range first.
Can SUMIF use OR logic?: Add two SUMIFs, or wrap criteria in an array constant: SUM(SUMIF(A:A,{"East","West"},C:C)).
Is SUMIF case sensitive?: No.
How do I sum by date with SUMIF?: Use a comparison with a cell reference, for example ">="&E1, where E1 holds the start date.