SUMIF Function

Adds up the cells that meet one condition.

Category

Math & Trig

Available in

All versions, Microsoft 365

Returns

A number

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Conditional Summaries module

Overview

SUMIF totals only the rows that match a condition — all sales in the West, every expense over $1,000, everything before a date.

For two or more conditions, use SUMIFS.

Syntax

=SUMIF(range, criteria, [sum_range])

Arguments

ArgumentRequired?What it does
rangeYesThe cells to test against the condition.
criteriaYesThe condition, such as "West", ">1000" or a cell like F2.
sum_rangeOptionalThe cells to add. Leave it out to add the tested cells themselves.

Examples

Example 1 — Sales for one region

Total the West region's orders.

E2fx=SUMIF(B2:B6, "West", C2:C6)
ABCDE
1RepRegionSalesWest total
2AnaWest6201550
3BenEast480
4CaraWest500
5DevNorth350
6EliWest430
=SUMIF(B2:B6, "West", C2:C6) Explain

Example 2 — Only the big orders

Add every sale of 500 or more. The tested cells and the summed cells are the same, so sum_range is left out.

C2fx=SUMIF(B2:B5, ">=500")
ABC
1RepSales500+ total
2Ana6201120
3Ben480
4Cara500
5Dev350
=SUMIF(B2:B5, ">=500") Explain

Example 3 — Condition in a cell

Join the operator and the cell with &.

=SUMIF(B2:B6, ">="&F1, C2:C6) Explain

Common mistakes

  • Operators go inside quotes. Write ">=500", not >=500. With a cell, join them: ">="&F1.
  • Ranges of different sizes. range and sum_range should be the same size; otherwise Excel quietly reshapes sum_range and totals can be wrong.

Notes

  • Criteria aren't case-sensitive and accept wildcards: "N*" matches North and Northeast.
  • Related functions: SUMIFS, COUNTIF, AVERAGEIF.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.