SUMIF & SUMIFS

How SUMIF works

SUMIF walks down a column, checks each cell against a condition, and adds up the matching values. The condition can be a literal value ("Office"), a comparison (">1000"), or a wildcard pattern ("Marketing*").

There are two columns involved: the column being checked, and the column being summed. They're often different (look at column B for the category, sum column C for the amount), but they can be the same when you skip the third argument.

SUMIFS is the multi-condition version. The argument order is reversed: the sum_range comes first, then pairs of (range, criteria). You can keep adding pairs for as many conditions as you need.

= SUMIF(range, criteria, [sum_range])

Arguments

SUMIF basic: total expenses by category

You're closing the books on Q1. The expense log has every transaction tagged with a category. The CFO wants a single number for office supplies. One SUMIF gives it to you in a heartbeat, no matter how many rows are on the log.

=SUMIF(B:B,"Office",C:C)

• •B:B → the column being checked (the Category column)
• •"Office" → the condition: only sum rows tagged Office
• •C:C → the column being summed (the Amount column)
• •Whole columns → B:B and C:C let the formula keep working as new rows are added

SUMIF with a comparison: total of every sale over $1,000

The criteria don't have to be a literal match. Wrap a comparison operator in quotes and SUMIF will sum the matching numeric values. This is the cleanest way to ask questions like "how much revenue came from large-ticket deals?"

=SUMIF(B:B,">1000")

• •B:B → the column of sale amounts
• •">1000" → the comparison, wrapped in quotes
• •No third argument → when sum_range is omitted, SUMIF sums the same range it checked
• •Operators → use >, <, >=, <=, =, <> all wrapped in quotes

SUMIF with wildcards: total spend by department prefix

Department codes in your GL system follow a pattern. Marketing accounts all start with "MKT-". Engineering with "ENG-". The wildcard character * matches any number of characters, so you can sum every row that starts with the same prefix.

=SUMIF(A:A,"MKT-*",B:B)

• •A:A → the department-code column
• •"MKT-*" → matches MKT- followed by anything: MKT-001, MKT-DIGITAL, MKT-Q3, etc.
• •B:B → the amount column being summed
• •? wildcard → matches exactly one character (less common but useful)
• •~ escape → use ~* to match a literal asterisk if your data contains them

SUMIFS: Q1 revenue by region (two conditions)

Real reports rarely depend on one filter. The CFO doesn't want all Q1 revenue, she wants Q1 revenue in the North region. SUMIFS handles that with a (range, criteria) pair for each condition. Watch the argument order: the sum_range comes first, not last.

=SUMIFS(D:D,A:A,"North",B:B,"Q1")

• •D:D → the sum_range: what you're adding (Revenue)
• •A:A, "North" → first condition: Region = North
• •B:B, "Q1" → second condition: Quarter = Q1
• •Pair pattern → every additional condition is another (range, criteria) pair
• •AND, not OR → SUMIFS only sums rows where all conditions are true

SUMIFS with a date range: revenue between two dates

A common reporting question is "how much did we sell between two dates?" SUMIFS handles this by using the same column twice with two different comparisons: greater than or equal to the start date, and less than the day after the end date.

=SUMIFS(C:C,A:A,">="&DATE(2024,1,1),A:A,"<"&DATE(2024,4,1))

• •C:C → the sum_range: Revenue
• •A:A, ">="&DATE(2024,1,1) → first condition: date is on or after Jan 1, 2024
• •A:A, "<"&DATE(2024,4,1) → second condition: date is before April 1, 2024
• •Same column twice → SUMIFS lets you reference the same range with multiple criteria
• •DATE() function → writes 2024-01-01 as a real date instead of risky text comparison

SUMIF vs SUMIFS: the argument order

The most confusing thing about these two functions is that the sum_range argument lives in different positions. Get this wrong and Excel either errors out or, worse, returns a wrong number with no warning.

Where people go wrong

1. 01
   Swapping SUMIF and SUMIFS argument order

   You write =SUMIFS(A:A,B:B,>1000) by muscle memory from SUMIF and end up summing the wrong column. SUMIFS puts the sum_range first, not last.

   Fix: When in doubt, type SUMIFS( and read the tooltip Excel pops up. The first argument is always sum_range. Then pairs.
2. 02
   Forgetting quotes around comparison criteria

   Writing =SUMIF(B:B,>1000) without the quotes returns a #NAME? error. Excel can't parse a bare comparison operator outside a string.

   Fix: Wrap the entire criteria in quotes: ">1000". For dynamic comparisons, use the ampersand: ">"&E2.
3. 03
   Mismatched range sizes

   SUMIF(B2:B100, ..., C2:C50) silently returns wrong results because the sum_range and the criteria range don't line up. SUMIFS errors out with #VALUE!

   Fix: Use whole columns (B:B and C:C) when you can. They auto-grow with new data and there's no chance of mismatch.
4. 04
   Wildcards on numeric ranges

   Trying =SUMIF(B:B,"1*",C:C) to match any number starting with 1 doesn't work. Wildcards only apply to text, and Excel stores numbers as numbers.

   Fix: Use comparison operators instead: ">=10" and "<20" for numbers in the teens. For text-formatted numbers, fix the data type at the source first.

Notes

• SUMIF was introduced in Excel 2003. SUMIFS arrived in Excel 2007. Both work in every modern version
• Wildcards in criteria: * matches any sequence of characters, ? matches exactly one character. Escape with ~ to match a literal asterisk or question mark
• Comparison operators in criteria must be wrapped in quotes: ">100", "<>0", ">="&E2 to splice a cell value
• Both functions are case-insensitive when comparing text: "office" matches "Office" and "OFFICE"
• An empty criteria string ("") matches blank cells. Use "<>" to match every non-blank cell
• When no rows match, both functions return 0 (not an error). Wrap in IFERROR only if your sum_range itself contains errors
• Whole-column references (B:B) are convenient and Excel optimises them well, but on millions of rows you may see a small slowdown compared to a tight range

What to learn next

Now prove it