SUMIF in Excel: Add Only the Rows That Match

SUMIF adds the numbers in rows that meet one condition. Total spend on Food. Total of invoices over $1,000. Sales for one region. It's one of the most useful formulas in everyday work.

Syntax

=SUMIF(range, criteria, [sum_range])

• range: where to look for the condition
• criteria: the condition
• sum_range: what to add (if left out, it adds range itself)

Example

=SUMIF(B2:B4,"Food",C2:C4)   → 70

Number conditions

Add only amounts over 100:

=SUMIF(C2:C100,">100")

Here the condition and the numbers are the same column, so sum_range isn't needed.

Condition in a cell

=SUMIF(B2:B100,F2,C2:C100)
=SUMIF(C2:C100,">"&F1)

Put category names down column F and fill the formula down for a full summary. Lock the ranges with $ first: =SUMIF($B$2:$B$100,F2,$C$2:$C$100). See absolute references.

Wildcards

=SUMIF(A2:A100,"*coffee*",C2:C100)

Adds rows where column A contains "coffee" anywhere.

The argument-order trap

SUMIF puts sum_range last. SUMIFS puts it first:

=SUMIF(B2:B100,"Food",C2:C100)
=SUMIFS(C2:C100,B2:B100,"Food")

Many people just use SUMIFS for everything so they only remember one order.

Common mistakes

• Ranges of different sizes: SUMIF quietly resizes sum_range, which can give wrong totals. Keep them the same size.
• Numbers stored as text in the amount column are skipped.
• Trailing spaces in categories ("Food ") don't match "Food". Clean with TRIM.

Real uses

• Monthly budget by category: see budget spreadsheet
• Sales per rep
• Hours per project
• Total of overdue invoices: =SUMIF(D2:D100,"<"&TODAY(),C2:C100)

Frequently asked questions

How do I sum based on a condition in Excel?

=SUMIF(condition_range,"condition",sum_range), e.g. =SUMIF(B:B,"Food",C:C).

What is the difference between SUMIF and SUMIFS?

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

Can SUMIF use a cell as the criteria?

Yes: =SUMIF(B:B,F2,C:C), or ">"&F2 for comparisons.

Is SUMIF case-sensitive?

No.