Excel SUMIF Function – Complete Guide | tubeshaala

Part 2 of 3 · Excel Functions Series

Excel SUMIF Function

Add values that meet a single condition — text match, number comparison, wildcard pattern, or date range.

=SUMIF(range, criteria, [sum_range])

What is the SUMIF Function?

SUMIF combines filtering and summing into one step. It scans a range for cells that match your criteria, then adds the corresponding values from a separate sum_range.

Think of it as asking Excel a conditional question: "From this dataset, give me the total only where a certain condition is true." Without SUMIF you would need helper columns, filters, or manual totals — all of which break the moment data changes.

◈

Prerequisite: You should be comfortable with =SUM() before using SUMIF. If not, complete Part 1 — SUM Function first. SUMIF is SUM with a condition layer added on top.

Syntax & Parameters

=SUMIF( range, criteria, [sum_range] )

range REQUIRED The column (or range) Excel checks against the criteria. This is your lookup column — e.g., the Region column, or the Category column.
criteria REQUIRED The condition to match. Can be a number (5000), text ("Delhi"), expression (">10000"), wildcard ("M*"), or a cell reference (F2).
[sum_range] OPTIONAL The column whose values get added when criteria is met. If omitted, Excel sums the range itself (only useful when range contains numbers).

ℹ

Key rule: range and sum_range must be the same size. If range is A2:A100 (99 rows), sum_range must also be 99 rows — e.g., B2:B100. Mismatched sizes cause silent wrong answers, not errors.

Anatomy of a SUMIF Formula

Breaking down a real example: Total sales from the Delhi region.

        =SUMIF(   range   ,   criteria   ,   sum_range )
      

① range

A2:A100

The Region column. Excel scans each cell here looking for "Delhi".

② criteria

"Delhi"

The exact text to match. Case-insensitive — "delhi", "DELHI" all match.

③ sum_range

B2:B100

The Sales column. Only the rows where A = "Delhi" get summed from here.

All Criteria Types

The criteria argument is flexible — it accepts six distinct types. Each is used differently in the formula.

① Exact Text

"Delhi"

Cells exactly equal to Delhi

"Laptop"

Cells exactly equal to Laptop

② Cell Reference

Uses whatever is typed in F2 as criteria — dynamic, no formula edits needed

③ Number

5000

Cells exactly equal to 5000

Cells that are zero

④ Comparison Operator

">10000"

Greater than 10,000

"<=5000"

Less than or equal to 5,000

"<>0"

Not equal to zero

⑤ Wildcard

"M*"

Starts with M (Mumbai, Mysore…)

"*Ltd"

Ends with Ltd

"*Pvt*"

Contains Pvt anywhere

⑥ Dynamic with &

">"&F2

Greater than the value in F2 (concatenate operator + reference)

"<"&TODAY()

Dates before today

⚠

Comparison operators must be in quotes: Writing >10000 causes a #VALUE! error. Always write it as ">10000". The operator and threshold must be a single quoted string.

Basic Examples

All examples below use this sales dataset (A1:C11):

A — Region

B — Salesperson

C — Sales (₹)

Region

Salesperson

Sales (₹)

Delhi

Arjun

45,000

Mumbai

Priya

62,500

Delhi

Rahul

58,200

Bangalore

Sneha

71,000

Delhi

Kabir

38,900

Mumbai

Anjali

29,400

Bangalore

Vikram

55,600

Delhi

Meera

41,300

Mumbai

Suresh

67,800

Bangalore

Nisha

43,200

=SUMIF(A2:A11,"Delhi",C2:C11)

1,83,400

Green rows are the ones Delhi matches. SUMIF adds only their C column values: 45,000 + 58,200 + 38,900 + 41,300 = ₹1,83,400.

Basic Sum by text match — using a cell reference for criteria

Instead of hardcoding "Delhi", reference a cell so users can change the region without editing the formula:

=SUMIF(A2:A11, F2, C2:C11)

Type "Mumbai" in F2 → result updates to Mumbai's total. Type "Bangalore" → switches instantly. This is the correct production approach.

Basic SUMIF without sum_range (range = numbers)

When the range you check IS also the column you want to sum, you can omit the third argument:

=SUMIF(C2:C11, ">50000") — sums all sales values in C that are greater than 50,000.

Here range and sum_range are the same column, so the shorthand is valid. Result: values where C > 50,000 are added directly.

Criteria Summing blanks and non-blanks

Goal	Formula
Sum where Region is blank	=SUMIF(A2:A11, "", C2:C11)
Sum where Region is NOT blank	=SUMIF(A2:A11, "<>", C2:C11)
Sum where Sales is zero	=SUMIF(C2:C11, 0)
Sum where Sales is NOT zero	=SUMIF(C2:C11, "<>0")

Wildcard Criteria

Wildcards let you match partial text — essential when your data has variations, codes, or prefixes.

Wildcard	Meaning	Example	Matches
`*`	Any sequence of characters (including none)	"M*"	Mumbai, Mysore, Mangalore, M
`?`	Any single character	"??lhi"	Delhi (2 chars + "lhi")
`~*`	Literal asterisk (escape)	"Rate~**"	Cells containing literal "Rate*"
`~?`	Literal question mark (escape)	"Q1~?"	Cells containing literal "Q1?"

Advanced Partial match — sum all "Pvt Ltd" companies

Your client column has entries like "Tata Pvt Ltd", "Infosys Pvt Ltd", "Reliance Pvt Ltd". To sum sales for all private limited companies:

=SUMIF(A2:A100, "*Pvt Ltd*", B2:B100)

The asterisks before and after mean anything can appear before "Pvt Ltd" or after it — only the substring must be present.

⚠

Wildcards only work with text. They have no effect on number ranges. "*5*" in a number column will not find cells containing the digit 5 — it returns 0 silently.

Date & Number Criteria

Dates Sum sales before / after a specific date

Goal	Formula
Sales before 1 April 2025	=SUMIF(A2:A100, "<"&DATE(2025,4,1), B2:B100)
Sales in FY 2024–25 (after cutoff)	=SUMIF(A2:A100, ">="&DATE(2024,4,1), B2:B100)
Sales before today	=SUMIF(A2:A100, "<"&TODAY(), B2:B100)
Sales on exact date in F2	=SUMIF(A2:A100, F2, B2:B100)

ℹ

Never type a date directly as text like "01-04-2025" — Excel may not recognise it as a date value. Always use DATE() or a cell reference for date criteria.

Numbers Sum based on numeric thresholds

Goal	Formula
Sales above ₹50,000	=SUMIF(C2:C11, ">50000")
Sales between ₹30K and ₹60K (two formulas)	=SUMIF(C2:C11,">=30000")-SUMIF(C2:C11,">60000")
Sum where threshold is in cell G1	=SUMIF(C2:C11, ">"&G1)
Negative values only	=SUMIF(C2:C11, "<0")

The "between" trick works by summing everything above the lower threshold, then subtracting everything above the upper threshold. This is a SUMIF limitation — SUMIFS handles ranges cleanly (Part 3).

Interactive Demo

Select a Region below. The table highlights matching rows, the formula updates, and the total recalculates — simulating exactly how SUMIF works in Excel.

▸ Live SUMIF Simulator

Region (criteria):

Row	A — Region	B — Salesperson	C — Sales (₹)
2	Delhi	Arjun	45,000
3	Mumbai	Priya	62,500
4	Delhi	Rahul	58,200
5	Bangalore	Sneha	71,000
6	Delhi	Kabir	38,900
7	Mumbai	Anjali	29,400
8	Bangalore	Vikram	55,600
9	Delhi	Meera	41,300
10	Mumbai	Suresh	67,800
11	Bangalore	Nisha	43,200

=SUMIF(A2:A11, "Delhi", C2:C11)

1,83,400

Common Errors & Fixes

Problem	Cause	Fix
Returns 0 (wrong)	Range and sum_range are different sizes — e.g., range is A2:A100 but sum_range starts at C1 instead of C2.	Make sure sum_range starts at the same row as range. Both should be A2:A100 and C2:C100.
Returns 0 (text issue)	Criteria text has extra spaces, or the range column has numbers stored as text.	Use `TRIM()` on both the criteria and range. Convert text-numbers via Data → Text to Columns.
#VALUE!	Comparison operator not in quotes — e.g., criteria written as `>5000` instead of `">5000"`.	Always wrap operators in double quotes: `">5000"`, `"<>0"`.
#NAME?	SUMIF is misspelled, or text criteria uses curly/smart quotes instead of straight quotes.	Retype the function. Check that your quotes are `"` (straight), not `"` or `"`.
Case-sensitive match needed	SUMIF is case-insensitive — "delhi" and "DELHI" are treated identically.	Use `=SUMPRODUCT((EXACT(A2:A11,"Delhi"))*C2:C11)` for case-sensitive summing.
Wildcard not working	Range contains numbers, not text. Wildcards only match text values.	Confirm the range column contains text. If numbers, use comparison operators instead.

Tips & Best Practices

🔗

Always use cell references for criteria

Instead of "Delhi" hardcoded in the formula, point to a cell like F2. This turns your SUMIF into a dynamic dashboard component — change F2, the answer changes instantly.

📐

Lock ranges with $ for copy-paste

If you plan to copy the SUMIF formula across rows or columns, use absolute references: =SUMIF($A$2:$A$100, F2, $C$2:$C$100). The criteria cell F2 stays relative; the data ranges stay fixed.

🔄

SUMIF is case-insensitive by design

"Delhi", "DELHI", and "delhi" all return the same result. This is usually convenient. If you need case-sensitive matching, switch to SUMPRODUCT + EXACT().

⚡

Entire column reference is fine but costly

=SUMIF(A:A,"Delhi",C:C) works but scans 10+ lakh rows. On large workbooks this adds calculation overhead. Use bounded ranges like A2:A10000 for better performance.

📊

Build a summary table with SUMIF

Put all region names in E2:E4, then in F2 write =SUMIF($A$2:$A$11, E2, $C$2:$C$11) and drag down. Instantly creates a regional summary table — no pivot table needed.

🚫

SUMIF cannot handle two conditions

If you need "Delhi AND Sales > 50,000", SUMIF cannot do it in one formula. That requires SUMIFS. See Part 3 of this series for the upgrade.

SUMIF vs SUM vs SUMIFS

Function	Use When	Conditions	Example
SUM	You want the total of everything in a range — no filter needed.	0 conditions	=SUM(C2:C11)
SUMIF	You want the total where exactly ONE column matches ONE condition.	1 condition	=SUMIF(A2:A11,"Delhi",C2:C11)
SUMIFS	You want the total where TWO OR MORE columns each match their own condition.	2–127 conditions	=SUMIFS(C2:C11,A2:A11,"Delhi",B2:B11,"Arjun")

✦

Rule of thumb: Always start with SUM. If you need filtering, upgrade to SUMIF. If you need multiple filters, upgrade to SUMIFS. Each function in this series is a direct extension of the previous one.

Limitation of SUMIF — Why SUMIFS Comes Next

SUMIF handles one condition precisely. The moment your business question involves two or more filters simultaneously, SUMIF breaks down.

Business Question	Can SUMIF do it?	Solution
Total sales for Delhi?	✅ Yes	=SUMIF(A2:A11,"Delhi",C2:C11)
Total Delhi sales by Arjun?	❌ No — two conditions	=SUMIFS(C:C,A:A,"Delhi",B:B,"Arjun")
Total Delhi sales above ₹40,000?	❌ No — two conditions	=SUMIFS(C:C,A:A,"Delhi",C:C,">40000")
Delhi + Mumbai combined total?	⚠ Workaround only (two SUMIFs)	=SUMIFS(C:C,A:A,"Delhi")+SUMIFS(C:C,A:A,"Mumbai")

→

SUMIFS (Part 3) accepts up to 127 condition pairs. It's the same logic as SUMIF — just extended. One important difference: in SUMIFS, the sum_range comes first, not last. Coming up next in this series.