Cellwise Try it free

Conditional function

Excel SUMIF function

SUMIF adds up the numbers in rows that meet one condition. Use it to total spending in one category, sales for one rep, or every expense over $100. For several conditions, use SUMIFS.

=SUMIF(range, criteria, [sum_range])

Arguments

range
The cells to test against the condition.
criteria
The condition, like "Groceries" or ">100". Text and operators go in quotes.
sum_range
Optional. The cells to add up. Leave it out to sum the range itself.

SUMIF examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1DateCategoryAmount
22025-06-02Groceries84.2
32025-06-03Fuel52
42025-06-05Dining out46.5
52025-06-07Groceries121.35
62025-06-09Utilities138
72025-06-12Groceries67.9
  1. =SUMIF(B2:B7,"Groceries",C2:C7)

    Result: 273.45

    Total grocery spending for the month.

  2. =SUMIF(C2:C7,">100")

    Result: 259.35

    No sum_range needed when you test and add the same column. This totals every bill over $100.

  3. =SUMIF(B2:B7,"<>Groceries",C2:C7)

    Result: 236.5

    Everything except groceries.

  4. =SUMIF(B2:B7,"D*",C2:C7)

    Result: 46.5

    Wildcards work too. This totals categories starting with D.

How to use SUMIF

  1. Click an empty cell.
  2. Type =SUMIF( and select the column to test.
  3. Type a comma and the condition in quotes.
  4. Type a comma and select the column to add.
  5. Close the bracket and press Enter.

Common SUMIF mistakes

Arguments in the wrong order

SUMIF puts sum_range last, but SUMIFS puts it first. Double-check when you switch between them.

Condition referencing a cell

Use ">"&E1, not ">E1". The second looks for the literal text E1.

sum_range a different size

Keep both ranges the same height. If they differ, Excel quietly resizes sum_range and can add the wrong cells.

SUMIF FAQ

Why is my SUMIF returning 0?

Usually the criteria never match because of extra spaces or different spelling, or the amounts are stored as text. Check one row by hand.

How do I use SUMIF with multiple criteria?

Use SUMIFS, which accepts several range and condition pairs. For an OR, add two SUMIFs together.

Can SUMIF sum cells that contain certain text?

Yes. Use wildcards in the condition, like =SUMIF(B2:B20,"*coffee*",C2:C20).

Practice SUMIF in these lessons

Guides that use SUMIF

Related functions

Updated