SUMIF and SUMIFS in Excel: Formulas With Examples

Q: Why does my SUMIF return 0?

The text may not match exactly, extra spaces may be present, or the numbers are stored as text.

SUMIF adds up numbers that meet one condition. SUMIFS handles several conditions at once. Here is how both work, with a small table you can copy.

The data used in the examples

Type this into cells A1 to C7:

	A: Region	B: Rep	C: Amount
1	Region	Rep	Amount
2	East	Ann	1200
3	West	Bob	800
4	East	Cara	450
5	North	Ann	950
6	West	Dan	600
7	East	Bob	300

SUMIF: sum with one condition

Syntax: =SUMIF(range, criteria, sum_range). Excel checks each cell in range, and when it matches criteria it adds the matching cell from sum_range.

Goal	Formula	Result
Total for East	`=SUMIF(A2:A7,"East",C2:C7)`	1,950
Total of amounts over 500	`=SUMIF(C2:C7,">500")`	3,550
Total for everyone except East	`=SUMIF(A2:A7,"<>East",C2:C7)`	2,350

When the range and the sum range are the same, as in the second example, you can leave out the third argument.

SUMIFS: sum with several conditions

Syntax: =SUMIFS(sum_range, range1, criteria1, range2, criteria2). Note the sum range comes first, the reverse of SUMIF. All conditions must be true.

Goal	Formula	Result
East sales by Ann	`=SUMIFS(C2:C7,A2:A7,"East",B2:B7,"Ann")`	1,200
East sales over 400	`=SUMIFS(C2:C7,A2:A7,"East",C2:C7,">400")`	1,650

Criteria cheat sheet

You want	Criteria
Exactly equal to text	“East”
Greater than a number	`">500"`
Greater than a cell value	`">"&E1`
Starts with E	`"E*"`
Not blank	`"<>"`
On or after a date	`">="&DATE(2027,1,1)`

Common mistakes

Ranges of different sizes in SUMIFS return #VALUE!. Every range must have the same number of rows.
Numbers stored as text are not added. Convert them to numbers first.
Operators in quotes: write “>500” with quotes, and join a cell with &, as in “>”&E1.
SUMIF cannot use OR across columns. Add two SUMIFs, or use SUMPRODUCT.

Count instead of sum? See COUNTIF in Excel. For a full report without formulas, try a pivot table.

Free pricing tips for your business

Get short, practical tips on pricing jobs and using our free calculators. No spam, unsubscribe any time.

Frequently asked questions

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition. SUMIFS takes many and puts the sum range first.

Can SUMIF use a cell as the criteria?

Yes. Use =SUMIF(A2:A7,E1,C2:C7) to read the text from E1, or “>”&E1 to compare numbers.

How do I sum by date range?

Use SUMIFS with two date conditions, for example “>=”&start and “<="&end on the date column.

Why does my SUMIF return 0?