Home › Excel functions › SUMIF

SUMIF Formula in Excel: How to Use It, with Examples

The SUMIF formula adds up the numbers in a range that meet a condition. Total sales for one region, expenses above a certain amount, hours per employee: if you need a sum with a filter, SUMIF is the function.

Excel CSE · 3 min read
In this guide
  1. SUMIF syntax
  2. Basic example
  3. Types of criteria
  4. Criteria in a cell
  5. SUMIFS: multiple criteria
  6. Common mistakes
  7. FAQ

SUMIF syntax

=SUMIF(range, criteria, [sum_range])
ArgumentWhat it means
rangeThe cells to check against the condition.
criteriaThe condition, such as "North", ">=800" or a cell like F2.
sum_range
(optional)		The cells to add up. Leave it out to add up the cells in range itself.

Basic example: sales per region

ABC
1RegionRepSales
2NorthAnna1,200
3SouthBen800
4NorthChris950
5EastDana450
6SouthAnna700
=SUMIF(A2:A6, "North", C2:C6)

Excel checks each cell in A2:A6. Where it says "North" (rows 2 and 4), it adds the sales from column C on the same row: 1,200 + 950 = 2,150.

The range and sum_range should be the same size and start on the same row, so each region lines up with its own sales figure.

Types of criteria

You wantFormulaResult
Exact text=SUMIF(A2:A6, "South", C2:C6)1,500
Everything except=SUMIF(A2:A6, "<>North", C2:C6)1,950
Numbers of at least 800=SUMIF(C2:C6, ">=800")2,950
Text starting with "A"=SUMIF(B2:B6, "A*", C2:C6)1,900
Non-empty cells=SUMIF(B2:B6, "<>", C2:C6)4,100

A few rules: conditions with an operator go between quotes (">=800"). In the third example there's no sum_range, so SUMIF adds up the values in C2:C6 itself. The wildcard * stands for any number of characters and ? for exactly one.

Put the condition in a cell

Hard-coded text works, but a cell reference lets you change the condition without editing the formula. With "North" in F2:

=SUMIF($A$2:$A$6, F2, $C$2:$C$6)

To combine an operator with a cell, join them with &. With 800 in F3, this adds all sales of at least 800:

=SUMIF(C2:C6, ">="&F3)

Writing ">=F3" inside the quotes doesn't work: Excel would look for the literal text "F3".

SUMIFS: multiple conditions

When you need more than one condition, use SUMIFS. Note that the order of the arguments is different: the range to add up comes first.

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Anna's sales in the South region:

=SUMIFS(C2:C6, A2:A6, "South", B2:B6, "Anna")

Only row 6 meets both conditions, so the result is 700. All conditions in SUMIFS must be true (AND logic). Many people use SUMIFS even for a single condition, so they only have to remember one argument order.

Common SUMIF mistakes

  • The result is 0. The criteria don't match anything. Check for extra spaces, or numbers stored as text in the sum range (text is ignored).
  • The total is off. range and sum_range have different sizes or start on different rows, so the values don't line up.
  • An operator with a cell doesn't work. Use ">"&F3, not ">F3".
  • Arguments in the wrong order in SUMIFS. In SUMIFS the sum range comes first; in SUMIF it comes last.

Frequently asked questions

What's the difference between SUMIF and SUMIFS?

SUMIF handles one condition; SUMIFS handles one or more. The argument order also differs: SUMIFS starts with the range to add up.

Is SUMIF case-sensitive?

No. "north", "North" and "NORTH" are all treated as the same.

How do I sum with an OR condition?

Add two SUMIFs together: =SUMIF(A2:A6, "North", C2:C6) + SUMIF(A2:A6, "South", C2:C6) totals both regions.

Can SUMIF sum between two dates?

Use SUMIFS with two conditions on the same date column: =SUMIFS(C2:C100, A2:A100, ">="&F1, A2:A100, "<="&F2), with the start date in F1 and the end date in F2.