The first thing most people do when they need to total sales for 'Acme Corp' is type =SUMIF(A2:A100,"Acme Corp",B2:B100) — and walk away satisfied. That’s dangerous. If column A contains "Acme Corp " (trailing space), "ACME CORP", or "Acme Corporation", SUMIF returns zero. No error. No warning. Just silence — and wrong numbers.
Quick Answer
SUMIF adds values in a range only when corresponding cells in another range meet a single condition — but it’s case-insensitive, supports wildcards (? and *), treats numbers and text differently, and ignores leading/trailing spaces in criteria unless explicitly included. Its syntax is =SUMIF(range, criteria, [sum_range]), where sum_range defaults to range if omitted.
All the Methods
Method
Steps
Best For
Limitations
Basic SUMIF
=SUMIF(A2:A12,"Sarah Chen",C2:C12)
Single exact-match text or number
Fails silently on extra spaces, partial matches without wildcards, or >1 condition
SUMIF with wildcards
=SUMIF(B2:B12,"*Q3*",D2:D12)
Fuzzy text matching (e.g., quarter codes, product families)
No support for ~ to escape * or ? inside strings unless doubled (e.g., "~*" → literal asterisk)
SUMIF with dates
=SUMIF(E2:E12,">="&DATE(2024,1,1),F2:F12)
Date ranges, month/year filtering
Can’t use DATEVALUE("1-Jan-2024") directly inside quotes — must concatenate or reference a cell
SUMIF with numeric conditions
=SUMIF(C2:C12,">50000",D2:D12)
Threshold-based totals (e.g., high-value deals)
Criteria like ">=50000" require quotes; no spaces before/after operators
SUMIF + TRIM (workaround)
=SUMIF(TRIM(A2:A12),"Sarah Chen",C2:C12) — array-enter with Ctrl+Shift+Enter
Dirty source data with inconsistent spacing
Not compatible with Excel Online or modern dynamic arrays unless wrapped in SUMPRODUCT
Method 1 Deep Dive
Let’s say you’re auditing Q3 vendor payments in Sheet1. Column A holds vendor names (A2:A12), B has invoice dates (B2:B12), C has amounts (C2:C12).
A (Vendor)
B (Date)
C (Amount)
Acme Corp
2024-07-12
$12,450
BetaTech Ltd
2024-08-03
$8,920
acme corp
2024-08-15
$15,600
Zeta Systems
2024-09-01
$22,100
ACME CORP
2024-09-18
$9,340
If you run =SUMIF(A2:A6,"Acme Corp",C2:C6), you’ll get $12,450 — only the first row matches *exactly*. The others fail because of case and spacing. But SUMIF is case-insensitive — so why doesn’t "acme corp" match? Because trailing space matters. "Acme Corp " ≠ "acme corp". The beauty of this approach is that you don’t need to clean all data upfront. Just wrap your criteria in TRIM and use SUMPRODUCT instead: =SUMPRODUCT(--(TRIM(A2:A6)="acme corp"),C2:C6). It returns $37,390 — all three rows. And yes, you can type "acme corp" in lowercase. SUMPRODUCT doesn’t care.
Method 2 Deep Dive
Now imagine you’re tracking regional sales across 2024. Column D holds region codes: "EMEA-2024-Q3", "APAC-2024-Q2", "NA-2024-Q3", etc. You want Q3 totals only.
Use =SUMIF(D2:D10,"*Q3*",E2:E10). Wildcards work here — * means “any number of characters”. So "EMEA-2024-Q3" and "NA-2024-Q3" both match. But what if you have "Q35" in your list? It would match too — *Q3* catches anything containing "Q3". To fix that, use =SUMIF(D2:D10,"*-Q3",E2:E10) — the dash ensures "Q3" appears after a hyphen, not mid-string. Even better: use =SUMIF(D2:D10,"????-????-Q3",E2:E10), where each ? stands for exactly one character. That forces a pattern like "XX-XXXX-Q3".
Here’s the counterintuitive part: SUMIF treats numbers stored as text differently. If E2:E10 contains "$12,450" (text with dollar sign and comma), SUMIF ignores it — even if the cell looks numeric. It only sums true numbers. To catch those, wrap in VALUE: =SUMIF(D2:D10,"*Q3*",--SUBSTITUTE(E2:E10,"$","")) — then array-enter (Ctrl+Shift+Enter) in legacy Excel. In Microsoft 365, just press Enter.
Cheat Sheet
What You Need
Formula
Shortcut / Tip
Exact match (case-insensitive)
=SUMIF(A2:A100,"Sarah Chen",B2:B100)
Alt+= inserts SUMIF automatically — then Tab to jump between arguments
Wildcard (starts with)
=SUMIF(A2:A100,"Beta*",B2:B100)
Type ~? or ~* to match literal ? or * (e.g., "Config~?v2")
Greater than date
=SUMIF(C2:C100,">="&DATE(2024,7,1),D2:D100)
Select cell with date → Alt+H+V+M to apply Date format — avoids string/date mismatch
Sum if blank
=SUMIF(A2:A100,"",B2:B100)
"" matches truly empty cells — not "" returned by formulas (use "=" for those)
Sum if not equal
=SUMIF(A2:A100,"<>Acme Corp",B2:B100)
<> must be inside quotes — no spaces: "<>Acme Corp", never "<> Acme Corp"
Multiple conditions?
Don’t use SUMIF — use SUMIFS instead
SUMIFS(A:A,B:B,"X",C:C,">100") — criteria pairs, order matters
Sarah Mitchell
Sarah has 12 years of experience covering Microsoft 365 productivity tools and enterprise software workflows. She specializes in Excel automation and SharePoint integration.
