SUMIFS Function in Excel: Sum With Multiple Conditions

📎 This article includes 2 downloadable practice files ↓

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. SUMIFS in practice
8. Where you will use it
9. Worked example
10. Mistakes people make
11. Related functions
12. Continue learning

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.

SUMIFS in practice

Where you will use it

• North sales in April
• GST totals by rate and month
• Expense by head and employee

Worked example

Region	Month	Sale	Formula	Result
North	Apr	12000	=SUMIFS($C$2:$C$4,$A$2:$A$4,A2,$B$2:$B$4,B2)	17000
North	May	18000	=SUMIFS($C$2:$C$4,$A$2:$A$4,A3,$B$2:$B$4,B3)	18000
North	Apr	5000	=SUMIFS($C$2:$C$4,$A$2:$A$4,A4,$B$2:$B$4,B4)	17000

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Sum range last (SUMIF habit)	In SUMIFS the sum range is first
Dates as text criteria	Use ">="&DATE(2026,4,1)

Related functions

SUMIF · COUNTIFS · SUMPRODUCT

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

Continue learning

All Math & Trig guides →

RANDARRAY Function in Excel: A Whole Grid of Random NumbersRANDARRAY returns an array of random numbers, optionally whole numbers between limits — instant…PERCENTOF Function in Excel: Share of the TotalPERCENTOF returns a subset’s share of a total — mostly used inside GROUPBY and…RAND Function in Excel: Random Decimal Between 0 and 1RAND returns a new random number between 0 and 1 on every recalculation —…RANDBETWEEN Function in Excel: Random Whole Numbers for Test DataRANDBETWEEN returns a random integer between two numbers. It recalculates on every change —…

📎 Practice files for this article

⬇ Download all 2 files (ZIP · 38 KB)

• 📗
  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
• 📗
  Practice workbook: Math & rounding24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 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.