How do I write SUMIFS criteria with operators or dates?

Join the operator to the value with &. For dates use ">="&DATE(2026,1,1); for numbers ">100" works as plain text. Never bake a cell reference inside the quotes — concatenate it: ">="&B3.

Can SUMIFS use wildcards?

Yes. Use * for any number of characters and ? for a single character, like "Kr*" to match every merchant starting with Kr. To match a literal asterisk or question mark, precede it with a tilde, as in "~*".

Why does my SUMIFS return 0?

Usually the amounts are text, not numbers (left-aligned, often with a green corner triangle), or the criteria don't match exactly — stray spaces, a different category spelling, or a date stored as text. Confirm the sum_range is numeric and the criteria values line up character for character.

Are there AVERAGEIFS and COUNTIFS too?

Yes — SUMIFS, AVERAGEIFS, and COUNTIFS are siblings that share the same multi-criteria syntax. COUNTIFS takes only criteria pairs (no sum range), while AVERAGEIFS mirrors SUMIFS exactly but averages instead of totals. See COUNTIF and COUNTIFS examples.

SUMIF vs SUMIFS: Which to Use

Last updated June 2026

Quick answer

Use SUMIFS for everything. It sums values on one condition or many, while SUMIF is limited to a single condition. The one trap is argument order: SUMIF(range, criteria, [sum_range]) puts the numbers you add last, but SUMIFS(sum_range, criteria_range1, criteria1, …) puts them first. The only reason SUMIF survives is legacy workbooks.

What is the difference between SUMIF and SUMIFS?

Both add up numbers that meet a condition. SUMIF accepts exactly one condition. SUMIFS accepts up to 127 condition pairs — and works perfectly with just one — so it does everything SUMIF does plus more. Microsoft added SUMIFS in Excel 2007; treat SUMIF as the older, weaker version.

The argument order is reversed — this is the real gotcha

The two functions don't just differ in capability; they read the arguments in opposite order. In SUMIF the range you test comes first and the range you add is an optional last argument. In SUMIFS the range you add comes first, followed by test/criteria pairs. Mixing them up is the most common reason a copied formula returns garbage.

Function	Signature	Where the numbers you add go
`SUMIF`	`SUMIF(range, criteria, [sum_range])`	Last (and optional)
`SUMIFS`	`SUMIFS(sum_range, criteria_range1, criteria1, …)`	First (and required)

Same job, opposite argument order. The sum_range moves from last to first.

The same scenario, written both ways

Say column C holds expense categories, column D holds amounts, and column A holds dates. To total all Food spending, either function works because there's one criterion:

=SUMIF(C2:C200,"Food",D2:D200)

SUMIF: test C2:C200 for Food, add the matching cells in D2:D200.

=SUMIFS(D2:D200,C2:C200,"Food")

SUMIFS: add D2:D200 where C2:C200 equals Food. The sum range leads.

Now total only the Food spending from 2026 onward. SUMIF cannot do this — it has no room for a second condition. SUMIFS just takes another criteria pair:

=SUMIFS(D2:D200,C2:C200,"Food",A2:A200,">="&DATE(2026,1,1))

Add D2:D200 where the category is Food and the date is on or after 1 Jan 2026. Stack as many pairs as you need — they combine with AND logic.

This is exactly the engine inside the expense tracker Summary sheet, where each cell totals one category for one month with =SUMIFS(Expenses!$D:$D,Expenses!$C:$C,$A4,Expenses!$A:$A,">="&B$3,Expenses!$A:$A,"<"&EDATE(B$3,1)) — category match plus a two-sided date window, copied across the grid.

How do you write a multi-criteria SUMIFS?

Lay out the data in columns
Put dates in A, categories in C, and amounts in D, with a header row. Keeping each attribute in its own column is what lets you filter on several at once.
Start with the sum_range
In an empty cell, type =SUMIFS( and select the column of numbers you want to total — here D2:D200. In SUMIFS this argument always comes first.
Add the first criteria pair
Type a comma, select C2:C200 (the range to test), another comma, then the value to match. Wrap text in quotes.
=SUMIFS(D2:D200,C2:C200,"Food")
Stack a second criteria pair for the date
Add another comma, select A2:A200, then a date condition. Build comparison criteria by joining the operator text to the value with &.
=SUMIFS(D2:D200,C2:C200,"Food",A2:A200,">="&DATE(2026,1,1))
Close and confirm
Type ) and press Enter. Each extra pair narrows the result with AND logic, so the total only counts rows matching every condition.
Lock ranges before copying across a grid
If you'll fill the formula across columns or down rows, anchor the data ranges with $ (for example $D:$D and $C:$C) so they don't drift, while leaving the criteria cells relative.

Frequently asked questions

How do I write SUMIFS criteria with operators or dates?: Join the operator to the value with &. For dates use ">="&DATE(2026,1,1); for numbers ">100" works as plain text. Never bake a cell reference inside the quotes — concatenate it: ">="&B3.
Can SUMIFS use wildcards?: Yes. Use * for any number of characters and ? for a single character, like "Kr*" to match every merchant starting with Kr. To match a literal asterisk or question mark, precede it with a tilde, as in "~*".
Why does my SUMIFS return 0?: Usually the amounts are text, not numbers (left-aligned, often with a green corner triangle), or the criteria don't match exactly — stray spaces, a different category spelling, or a date stored as text. Confirm the sum_range is numeric and the criteria values line up character for character.
Are there AVERAGEIFS and COUNTIFS too?: Yes — SUMIFS, AVERAGEIFS, and COUNTIFS are siblings that share the same multi-criteria syntax. COUNTIFS takes only criteria pairs (no sum range), while AVERAGEIFS mirrors SUMIFS exactly but averages instead of totals. See COUNTIF and COUNTIFS examples.