Tutorials›Excel›SUMIF & COUNTIF
🟢 Free Demo
Excel TutorialTopic 6 of 31

SUMIF & COUNTIF

Sum and count data that meets specific conditions — the backbone of every Excel report and dashboard.

✅ What You Will Learn

›How SUMIF adds values that match one condition
›How COUNTIF counts cells that match one condition
›How SUMIFS and COUNTIFS handle multiple conditions
›How to use wildcard characters (* and ?) in criteria
›How to use comparison operators inside SUMIF criteria

SUMIF adds numbers from one range only where a corresponding range meets a condition. COUNTIF counts cells that meet a condition. These two functions handle the vast majority of conditional analysis in Excel — things like "total sales for the North region", "how many orders were above ₹10,000", or "count of employees in the Finance department".

The multi-condition versions — SUMIFS and COUNTIFS — follow the same logic but accept up to 127 condition pairs. The argument order in SUMIFS is slightly different from SUMIF: in SUMIFS the sum range comes first, before the criteria ranges.

Mastering these four functions lets you build summary reports and dashboards directly from raw data without manually filtering or creating pivot tables.

📋 Order data — source for SUMIF and COUNTIF examples

Order IDRegionCategoryAmount (₹)Status
O-101NorthElectronics45000Delivered
O-102SouthAccessories3200Delivered
O-103NorthElectronics62000Pending
O-104EastAccessories8500Delivered
O-105SouthElectronics28000Cancelled
O-106NorthAccessories5500Delivered

Syntax

EXCEL SYNTAX
=SUMIF(range, criteria, [sum_range])
  If sum_range is omitted, the range itself is summed.

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

=COUNTIF(range, criteria)

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Criteria examples:
  "North"        → exact text match
  ">10000"       → greater than 10000
  "<="&D1        → less than or equal to value in D1
  "Jan*"         → starts with Jan (wildcard)
  "???"          → exactly 3 characters

Examples

Example 1SUMIF — total sales for the North region only
=SUMIF(B2:B7, "North", D2:D7)
OUTPUT
112500   (45000 + 62000 + 5500)
💡

The first range (B2:B7) is checked. Where it equals "North", the matching value from D2:D7 is added to the total.

Example 2SUMIFS — total delivered Electronics orders
=SUMIFS(D2:D7, C2:C7, "Electronics", E2:E7, "Delivered")
OUTPUT
45000   (only O-101 matches both conditions)
💡

SUMIFS checks ALL conditions simultaneously. Both criteria must match for a row to be included in the sum.

Example 3COUNTIF with comparison operator — orders above ₹10,000
=COUNTIF(D2:D7, ">10000")
OUTPUT
3   (45000, 62000, 28000)
💡

Comparison operators inside COUNTIF criteria must be inside double quotes as a text string: ">10000" not >10000.

📌 Key Points to Remember

  • ✓SUMIF argument order: (check_range, condition, sum_range). SUMIFS: (sum_range, check_range1, condition1, ...)
  • ✓Criteria text must be in double quotes. Number criteria can be bare numbers or strings like ">5000"
  • ✓Use & to combine operators with cell references: ">="&B1 means "greater than or equal to the value in B1"
  • ✓Wildcards: * matches any sequence of characters, ? matches exactly one character
  • ✓COUNTIFS with no sum_range argument — it only counts, never sums

🏢 Real-World Application

Every weekly MIS report in Indian companies uses SUMIF or SUMIFS. A typical report has a summary sheet where SUMIFS pulls region-wise, category-wise, and status-wise totals from a raw data sheet with thousands of rows. What would take hours of manual filtering runs in milliseconds with these functions.

⚠️ Common Mistakes to Avoid

WRONGWriting =SUMIF(B:B, "North", D:D) on a million-row sheet and wondering why Excel is slow
FIXLimit ranges to your actual data: B2:B5000 instead of B:B. Full-column references force Excel to check every one of the million rows.
WRONGSwapping argument order between SUMIF and SUMIFS
FIXSUMIF: (range, criteria, sum_range). SUMIFS: (SUM_RANGE first, then criteria pairs). The difference trips everyone up — check the function hint bar.
WRONGForgetting quotes around comparison operators: =COUNTIF(D:D, >10000)
FIXComparison criteria must be text strings in quotes: =COUNTIF(D:D, ">10000"). Without quotes you get a #VALUE! error.
✏️Test Yourself

What is the correct syntax to sum column C where column A equals "Delhi" AND column B is greater than 1000?

❓ Frequently Asked Questions

What is the difference between SUMIF and SUMIFS?

SUMIF handles one condition. SUMIFS handles one or more conditions. The argument order differs — in SUMIFS the sum_range comes first.

Can I use cell references instead of hardcoded criteria?

Yes. =SUMIF(B:B, G1, D:D) uses whatever is in G1 as the condition. For operators with cell references use: ">"&G1.

How do I count cells that are not empty?

=COUNTIF(A:A,"<>") counts all non-blank cells. The <> operator means "not equal to" — with nothing after it, it means "not empty".

✏️ Practice Exercise

Using an order dataset with columns: Date, Region, Salesperson, Product Category, Amount — write SUMIFS formulas to answer: (1) Total amount for North region in Electronics, (2) Total delivered orders above ₹20,000, (3) Count of orders by each salesperson. Build a small summary table using these formulas.

← PreviousINDEX & MATCHNext →Text Functions
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.