Your manager asks, “What is the total sales for West region and how many pending orders do we have?” You could filter and calculate manually, but SUMIF and COUNTIF give the answer instantly and update automatically.

What does SUMIF do?

SUMIF adds numbers only when a condition is met. For example, it can add sales only for West region or only for Electronics category.

SUMIF structure

=SUMIF(criteria_range, criteria, sum_range)

Example: =SUMIF(C:C,"West",I:I) adds Sales from column I only where Region in column C is West.

What does COUNTIF do?

COUNTIF counts cells that meet one condition. For example, it can count pending orders, closed orders, or records from a specific region.

Example: =COUNTIF(J:J,"Pending") counts how many rows have Pending status in column J.

Simple rule: SUMIF gives total amount. COUNTIF gives number of matching records.

Business examples

QuestionFormulaUse
Total sales for West?=SUMIF(C:C,"West",I:I)Regional sales
How many Pending orders?=COUNTIF(J:J,"Pending")Order tracking
Total Electronics sales?=SUMIF(F:F,"Electronics",I:I)Category report
SUMIFTotal by condition
COUNTIFCount by condition
A4Reusable criteria

Use cell references as criteria

Instead of typing "West" inside the formula, you can use a cell reference like A4. This makes the formula reusable for multiple rows.

Typed criteria=SUMIF(C:C,"West",I:I)
Cell criteria=SUMIF(C:C,A4,I:I)
💡
Professional habit: Use cell references for summary tables so you can copy formulas down for multiple regions, statuses or categories.
⚠️
Common mistake: Criteria spelling mismatch. “Pending”, “pending”, and “Pendng” can cause wrong summaries.
🧪 Practice Lab

Create condition-based sales summaries

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

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

Mini assignment

  1. Create a region-wise sales summary using SUMIF.
  2. Create region-wise order count using COUNTIF.
  3. Create status-wise sales summary.
  4. Find total Electronics sales.
  5. Count Pending orders.
  6. Verify summary totals against overall sales.

Quick quiz

  1. What does SUMIF do?
  2. What does COUNTIF do?
  3. What are the three parts of SUMIF?
  4. Why is cell criteria useful?

Answers: Adds by condition; counts by condition; criteria range, criteria, sum range; makes formulas reusable.

FAQ

SUMIF handles one condition. For multiple conditions, Excel has SUMIFS, which will be covered later.
Yes. COUNTIF can count cells that match text, numbers or simple conditions.