SUMIF vs SUMIFS

SUMIF vs SUMIFS

Understand the difference between SUMIF and SUMIFS and choose the right Excel formula for single-condition or multi-condition totals.

Quick verdict

  • Use SUMIF for one condition. Use SUMIFS when totals depend on multiple criteria such as region, product, date, or status.

Formula structure

  • SUMIF uses range, criteria, then optional sum_range.
  • SUMIFS starts with sum_range, then accepts one or more criteria ranges and criteria pairs.

Common use cases

  • Use SUMIF for totals by one category, such as sales for one product.
  • Use SUMIFS for totals by product, region, sales rep, and date range together.

Overview

  • SUMIF is for one rule; SUMIFS is for multiple AND conditions. The argument order changes — that swap causes more errors than the logic itself.

Syntax contrast

  • SUMIF: =SUMIF(criteria_range, criteria, sum_range).
  • SUMIFS: =SUMIFS(sum_range, criteria_range1, criteria1, ...).
  • Memory trick: SUMIFS starts with what you sum.

Build-up exercise

  • Step 1 — East only: SUMIF.
  • Step 2 — East AND Closed: SUMIFS with two pairs.
  • Step 3 — add date window as third and fourth criteria.

Parallel counting

  • Same filters with COUNTIFS for row counts.
  • See [COUNTIF vs COUNTIFS](/compare/countif-vs-countifs/).

Quick answer: sumif vs sumifs

  • SUMIF handles exactly one condition. SUMIFS handles multiple conditions and puts sum_range first.
  • One filter → SUMIF.
  • Two or more filters → SUMIFS.
  • Mirror with COUNTIF / COUNTIFS for counts on the same rules.

Building criteria pairs step by step

  • Step 1 — one condition with SUMIF: =SUMIF(Region,"East",Amount).
  • Step 2 — add a second condition with SUMIFS: =SUMIFS(Amount,Region,"East",Status,"Closed").
  • Step 3 — add a date filter: =SUMIFS(Amount,Region,"East",Date,">="&StartDate).

Parallel with COUNTIF family

  • After SUMIFS totals, use COUNTIFS for volume KPIs on the same filters.
  • See [COUNTIF vs COUNTIFS](/compare/countif-vs-countifs/) for the counting equivalent.

People also ask

  • When is SUMIF enough? — One filter only, such as one region or one status.
  • Can SUMIFS reference different sheet? — Yes if ranges are same size and shape.

Frequently asked questions

  • When is SUMIF enough? Exactly one condition — one region, one status, one rep.
  • Can SUMIFS reference different sheets? Yes if all ranges are the same size and shape.
  • SUMIF returns wrong total with filters applied? SUMIF ignores AutoFilter. Use SUBTOTAL or filter-aware helpers.
  • When is SUMIF enough? When you have exactly one criterion (one region, one status, one product line). Add SUMIFS when you need two or more filters.
  • Do SUMIF and SUMIFS use the same argument order? No. SUMIF is (range, criteria, sum_range). SUMIFS starts with sum_range, then criteria_range/criteria pairs.
  • Can criteria reference other cells? Yes — concatenate operators: =SUMIF(A:A,">"&F1,B:B) and the same pattern for each SUMIFS criteria pair.