Excel function

SUMIF in Excel: add values that match one condition

SUMIF adds numbers only when one condition is true. It is a practical bridge between basic SUM formulas and more advanced conditional summaries like SUMIFS.

What SUMIF does

SUMIF adds values based on one condition. The syntax is `=SUMIF(range, criteria, [sum_range])`. Excel checks the criteria range, finds matching rows, and adds the corresponding values from the sum range.

For example, `=SUMIF(B2:B100,"North",D2:D100)` adds values in D2:D100 only for rows where B2:B100 is North. If the range you test is also the range you want to add, the sum range can be omitted.

SUMIF is useful for simple totals by category, status, person, class, region, or threshold. It teaches the same criteria logic used by SUMIFS, COUNTIF, and COUNTIFS.

Arguments explained

`range` is the criteria range, where Excel checks the condition. `criteria` is the rule, such as `"Paid"`, `"North"`, `">100"`, or a cell reference like F2. `sum_range` is the range containing the numbers to add.

Text criteria need quotation marks. Numeric comparison criteria also use quotation marks when they include an operator: `">=500"`. If you compare against a cell, join the operator and cell reference: `">="&F2`.

SUMIF differs from SUMIFS in both purpose and syntax. SUMIF handles one condition and puts the criteria range first. SUMIFS handles multiple conditions and puts the sum range first.

That argument order is worth practicing. Many mistakes happen because learners switch from SUMIF to SUMIFS and keep the old order. Read the formula aloud: "check this range for this condition, then add this amount range."

Practical examples

A beginner school example is totaling points for one team. If B2:B30 contains Team and C2:C30 contains Points, `=SUMIF(B2:B30,"Blue",C2:C30)` adds points for Blue.

A business example is totaling paid invoice amounts. If C2:C200 contains Status and E2:E200 contains Amount, `=SUMIF(C2:C200,"Paid",E2:E200)` returns total paid revenue. This can feed a finance tracker or monthly report.

A numeric example is summing orders above a threshold. `=SUMIF(D2:D100,">=1000",D2:D100)` adds order amounts that are at least 1000.

For practice, try changing the criteria from typed text to a cell reference. Putting the target region in F2 makes the formula easier to reuse across a summary table.

When to use it

Use SUMIF when the question has one condition: total sales for one region, points for one team, hours for one employee, or invoices with one status. It is simpler than SUMIFS for one-condition tasks.

Do not use SUMIF when you need two or more criteria, such as region and month. Use SUMIFS instead. Do not confuse criteria range and sum range, and avoid manually filtering rows before summing if a formula should update automatically.

Practical examples

Sum revenue for North

=SUMIF(B2:B100,"North",D2:D100)

Checks B2:B100 for North and adds the matching values from D2:D100.

Sum amounts above a threshold

=SUMIF(D2:D100,">=1000",D2:D100)

Adds only order amounts in D2:D100 that are at least 1000.

Recommended Excel functions

SUMIFSUMIFSCOUNTIFCOUNTIFSIF

Common mistakes

  • 01Using SUMIF for multiple conditions instead of SUMIFS.
  • 02Mixing up the criteria range and sum range.
  • 03Forgetting quotation marks around text or comparison criteria.
  • 04Using ranges that do not line up row by row.

Practice

Practice this skill with interactive exercises on Calcu.

Use Calcu to practice SUMIF in realistic spreadsheet exercises with instant feedback.

Open Calcu

FAQ

What is SUMIF used for?

SUMIF adds values that meet one condition, such as one region, status, category, or threshold.

What is the difference between SUMIF and SUMIFS?

SUMIF handles one condition. SUMIFS handles multiple conditions and uses a different argument order.

Can SUMIF use numeric criteria?

Yes. SUMIF can use criteria like ">100", "<=500", or ">="&F2.