Your manager asks, “What is the total sales for West region, but only for Closed orders?” A normal SUMIF handles one condition. SUMIFS handles multiple conditions and gives the answer cleanly.

What does SUMIFS do?

SUMIFS adds numbers only when multiple conditions are met. For example, it can add sales where Region is West and Status is Closed.

SUMIFS structure

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

Example: =SUMIFS(J:J,D:D,"West",K:K,"Closed")

This adds Sales from column J where Region in column D is West and Status in column K is Closed.

What does COUNTIFS do?

COUNTIFS counts records that meet multiple conditions. For example, it can count orders where Month is Aug and Status is Pending.

Example: =COUNTIFS(C:C,"Aug",K:K,"Pending")

Simple rule: SUMIFS totals matching rows. COUNTIFS counts matching rows.

Business examples

QuestionFormulaUse
Total West Closed sales=SUMIFS(J:J,D:D,"West",K:K,"Closed")Region + Status
Count Aug Pending orders=COUNTIFS(C:C,"Aug",K:K,"Pending")Month + Status
Total Aug Electronics sales=SUMIFS(J:J,C:C,"Aug",G:G,"Electronics")Month + Category
SUMIFSTotal with many conditions
COUNTIFSCount with many conditions
2+Criteria pairs

Use cell criteria for reusable summaries

Instead of typing criteria inside formulas, use cells like A4 and B4. This lets you copy the formula down for different combinations.

Typed criteria=SUMIFS(J:J,D:D,"West",K:K,"Closed")
Cell criteria=SUMIFS(J:J,D:D,A4,K:K,B4)
💡
Professional habit: Build summary tables with criteria columns first, then formulas. This makes reports easier to audit.
⚠️
Common mistake: Putting criteria ranges in different sizes or misaligned columns. Keep all ranges aligned with the source data.
🧪 Practice Lab

Create multi-condition sales summaries

Download one ZIP file containing the practice workbook, challenge workbook, solution workbook, formula cheat sheet, quiz and answer key.

📦 Download Lesson 17 ResourcesWorks as a standalone file and after website deployment

Mini assignment

  1. Create Region + Status summary using SUMIFS.
  2. Create Region + Status order count using COUNTIFS.
  3. Create Month + Category sales summary.
  4. Answer the business questions in the practice workbook.
  5. Use cell references instead of typing all criteria manually.

Quick quiz

  1. What does SUMIFS do?
  2. What does COUNTIFS do?
  3. When should you use SUMIFS instead of SUMIF?
  4. Why are cell criteria useful?

Answers: Adds values with multiple conditions; counts records with multiple conditions; when more than one condition is required; reusable summary formulas.

FAQ

Yes, but if there is only one condition, SUMIF is simpler. SUMIFS is better when you expect multiple conditions.
Yes. COUNTIFS can count based on text, numbers, dates and simple comparison criteria.