Math & Trig

SUMIF Function in Excel

Adds the cells specified by a given condition or criteria.

Syntax

  • =SUMIF(range, criteria, [sum_range])

Arguments

  • range (required): The range of cells to evaluate
  • criteria (required): The condition to match
  • sum_range (optional): The cells to sum (if different from range)

Examples

  • =SUMIF(A:A, "Apple", B:B) - Sum sales for Apple products - Result: $5,000
  • =SUMIF(B:B, ">100") - Sum values greater than 100 - Result: 1,500

Overview

  • SUMIF totals values where a single criterion is true — one region, one status, one product line. Master criteria operators and cell references before moving to SUMIFS.

Reporting formulas that work in production

  • Sum East region: =SUMIF(Region,"East",Amount).
  • Amount over threshold in F1: =SUMIF(Amount,">"&F1,Amount).
  • Partial product name: =SUMIF(Product,"*widget*",Revenue).

Common reasons SUMIF returns 0

  • Criteria text does not exactly match (extra spaces).
  • Dates stored as text — confirm with ISNUMBER.
  • criteria_range and sum_range different heights.

Upgrade path

  • Two filters → [SUMIFS](/functions/sumifs/).
  • Compare: [SUMIF vs SUMIFS](/compare/sumif-vs-sumifs/).
  • Debug: [SUMIF not working](/problems/sumif-not-working/).

Quick answer: sumif excel

  • SUMIF adds cells in sum_range where criteria_range meets one condition. Argument order is criteria_range, criteria, sum_range.
  • Open
  • >
  • Two or more filters require SUMIFS instead.

SUMIF best practices

  • Use SUMIF for one condition, such as totals for one product, team, status, or region.
  • Use SUMIFS when the total depends on more than one condition.
  • Make sure range and sum_range are the same shape to avoid incorrect totals.

Criteria from a cell with & concatenation

  • Greater than value in F1: =SUMIF(A:A,">"&F1,B:B).
  • Text match from G2: =SUMIF(Status, G2, Amount).
  • Partial text: =SUMIF(Product,"*"&H2&"*",Revenue).
  • Date on or after start: =SUMIF(OrderDate,">="&StartDate,Amount).

When to upgrade to SUMIFS

  • Add SUMIFS when you need region AND product, status AND month, or rep AND quarter in one total.
  • SUMIFS argument order is sum_range first, then criteria_range and criteria pairs.
  • See [SUMIF vs SUMIFS](/compare/sumif-vs-sumifs/) and [SUMIFS examples](/blog/sumifs-excel-examples/).

If SUMIF returns 0 or wrong totals

  • Check that criteria text exactly matches the source column spelling.
  • Confirm criteria_range and sum_range are the same height and start on the same row.
  • Walk through [SUMIF not working](/problems/sumif-not-working/) before hiding errors with IFERROR.

People also ask

  • Why does SUMIF return 0? — Criteria mismatch, text dates, or misaligned ranges.
  • Can SUMIF use two criteria? — No; use SUMIFS.

Common errors

  • #VALUE! with incorrect criteria format

Use cases

  • Conditional totals
  • Category summaries
  • Filtered calculations

Frequently asked questions

  • What is the argument order for SUMIF? SUMIF(criteria_range, criteria, sum_range). Note SUMIFS puts sum_range first — do not mix them up.
  • Does SUMIF sum only visible filtered rows? No. SUMIF includes hidden rows. Use SUBTOTAL on visible cells or SUMIFS on a helper column for filter-aware totals.
  • Can SUMIF criteria reference another cell? Yes. Use concatenation: =SUMIF(A:A,">"&F1,B:B).
  • What is SUMIF in Excel and how do I use it? SUMIF adds cells that meet a specific condition. The syntax is =SUMIF(range, criteria, [sum_range]). For example, =SUMIF(A:A, "Apple", B:B) sums values in column B where column A equals "Apple". If sum_range is omitted, it sums the cells in the criteria range itself. SUMIF is essential for conditional totals in data analysis.
  • What is the difference between SUMIF and SUMIFS? SUMIF handles one condition, while SUMIFS handles multiple conditions. SUMIF syntax: =SUMIF(range, criteria, sum_range). SUMIFS syntax: =SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...). Note that in SUMIFS, the sum_range comes FIRST. Example: =SUMIFS(C:C, A:A, "Sales", B:B, ">1000") sums column C where A is "Sales" AND B is greater than 1000.
  • How do I use wildcards in SUMIF? SUMIF supports wildcards: * (any characters) and ? (single character). Examples: =SUMIF(A:A, "*apple*", B:B) sums where A contains "apple"; =SUMIF(A:A, "App??", B:B) matches "Apple" or "Appli" (5 characters starting with App). To find literal * or ?, use ~* or ~?. Wildcards only work with text criteria.
  • Why is SUMIF returning 0 when there are matching values? SUMIF returns 0 when: 1) Criteria doesn't match due to extra spaces - use TRIM(); 2) Numbers stored as text or vice versa; 3) Criteria range and sum_range have different sizes; 4) Criteria syntax is wrong - numbers need quotes for operators: ">100" not >100; 5) The criteria range doesn't contain the lookup value. Check data types and formatting.
  • How do I SUMIF with multiple OR conditions? SUMIF doesn't support OR directly. Solutions: 1) Add multiple SUMIFs: =SUMIF(A:A,"Apple",B:B)+SUMIF(A:A,"Orange",B:B); 2) Use SUMPRODUCT: =SUMPRODUCT((A:A="Apple")+(A:A="Orange"))*B:B); 3) Use array formula with SUM: =SUM(SUMIF(A:A,{"Apple","Orange"},B:B)). For AND conditions, use SUMIFS instead.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.