📎 This article includes 1 downloadable practice file ↓

MathLevel: IntermediateAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Power combo
6. Common errors and fixes
7. Related functions

SUMIFS adds values where all conditions are true — the backbone of formula-based reports.

Syntax

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

Argument	What it means
sum_range	Cells to add — comes first, unlike SUMIF.
criteria_range1, criteria1	First range and its condition.
…	Up to 127 range/condition pairs.

Examples

Example 1

=SUMIFS(E:E, B:B, "North", C:C, "Retail")

North retail sales.

Example 2

=SUMIFS(E:E, A:A, ">="&DATE(2016,4,1), A:A, "<"&DATE(2016,5,1))

April 2016 total.

Power combo

=SUM(SUMIFS(E:E, B:B, {"North","East"}))

OR logic: North or East.

Common errors and fixes

You see	Why, and the fix
#VALUE!	Ranges of different sizes.
Total is 0	Numbers stored as text, or trailing spaces in the criteria column.

Related functions

SUMIF · COUNTIFS · SUMPRODUCT

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

📎 Practice files for this article

• 📗
  SUMIFS/COUNTIFS scenarios workbookConditional sums and counts with dates, OR logic and wildcards, plus 4 classic reasons totals come out wrong — all with PASS/FAIL checks.
  ⬇ XLSX · 9 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

Keep reading

Excel Functions A–ZXIRR Function in Excel: Real Return on SIPs and Irregular Cash FlowsExcel Functions A–ZRANK.EQ Function in Excel: Rank a Number in a ListExcel Functions A–ZNETWORKDAYS Function in Excel: Working Days Between Two Dates