SUMIF answers the question every report eventually asks: “What is the total for this one category?” It scans a list, finds the rows that match a condition, and adds only those numbers.
This guide covers the syntax, every comparison operator, wildcards, cell-reference criteria, five real-world examples, the SUMIF vs SUMIFS decision, date-based totals (monthly, quarterly, YTD), cross-sheet summing, alternatives like SUMPRODUCT and FILTER, a dashboard walkthrough, and the errors that cause wrong totals.
SUMIF adds the values in a range that meet a single condition, written as =SUMIF(range, criteria, [sum_range]).
What Is the SUMIF Function?
SUMIF is a conditional sum. It looks through a criteria range, finds every cell that matches your condition, and adds the corresponding values from a separate sum range.
If you leave out the sum range, SUMIF adds the criteria range itself. That makes it useful for simple totals such as “sum all amounts above 1000” without repeating the same range twice.
How SUMIF evaluates a formula
Take the formula =SUMIF(A2:A10,"East",B2:B10). Excel does three things, in order:
- Scan the criteria range. It reads every cell in
A2:A10and asks: does this cell match “East”? - Mark the matching rows. Every row where the test is TRUE gets flagged.
- Add the sum range. It adds the values in
B2:B10for those flagged rows only — and ignores everything else.
“East”, “east”, and “EAST” all match. If you need case-sensitive summing, use SUMPRODUCT with EXACT.
Syntax and Arguments
The full signature has three arguments. Only the last one is optional.
=SUMIF(range, criteria, [sum_range])
Walking through each argument
- range — the cells you want to test. Usually a column of text, numbers, or dates.
- criteria — the condition to match. Can be a number, text, expression, cell reference, or function. Text and operators must be in double quotes.
- sum_range (optional) — the cells to add when the criteria match. If you omit it, Excel sums the
rangeitself.
| Argument | Required? | Notes |
|---|---|---|
range | Required | Same height as sum_range whenever possible. |
criteria | Required | Text and operators in double quotes: "East", ">1000". |
sum_range | Optional | If omitted, SUMIF adds the criteria range itself. |
=SUMIF(A2:A10,East,B2:B10) makes Excel look for a defined name called East. It fails with #NAME?. Use "East" instead.
Criteria Operators and Wildcards
The criteria argument is where SUMIF gets its flexibility. You can use comparison operators, text, numbers, dates, wildcards, and cell references.
The six comparison operators
| Operator | Meaning | Example criteria | Matches when… |
|---|---|---|---|
= | Equal to | "East" | Cell equals East |
> | Greater than | ">100" | Cell is above 100 |
< | Less than | "<0" | Cell is negative |
>= | Greater or equal | ">=1000" | Cell is 1000 or more |
<= | Less or equal | "<=50" | Cell is 50 or less |
<> | Not equal to | "<>East" | Cell is not East |
Wildcard characters
Three wildcards work when the criteria is text. They are useful for partial matches without knowing the exact value.
Any number of characters
"North*" matches North, Northeast, Northwest. Use it when the text starts, ends, or contains a phrase.
Exactly one character
"A?C" matches ABC, AXC, and A1C. Use it for fixed-length codes with one unknown character.
Escape a wildcard
"~*" matches a literal asterisk. Use it when your data actually contains * or ?.
' Text match
=SUMIF(A2:A20,"East",B2:B20)
' Number comparison
=SUMIF(B2:B20,">1000")
' Date from a real date value
=SUMIF(A2:A20,">="&DATE(2026,1,1),B2:B20)
' Cell reference as criteria
=SUMIF(A2:A20,E2,B2:B20)
' Operator plus cell reference
=SUMIF(A2:A20,">"&E2,B2:B20)
' Wildcard: starts with North
=SUMIF(A2:A20,"North*",B2:B20)
' Not blank
=SUMIF(A2:A20,"<>",B2:B20)
If E2 contains 1000, write ">"&E2. Writing ">E2" looks for the literal text “>E2” and returns 0.
Real-World Examples
These five patterns cover most day-to-day SUMIF use. Each one shows the data layout, the formula, and the result you should see.
Example 1 — Sales by region
| A Region | B Sales | C Total East | |
|---|---|---|---|
| 2 | East | 1200 | =SUMIF(A2:A6,"East",B2:B6) |
| 3 | West | 900 | |
| 4 | East | 1500 | |
| 5 | North | 700 | |
| 6 | East | 1100 | 3800 |
=SUMIF(A2:A6,"East",B2:B6) → 1200 + 1500 + 1100 = 3800Example 2 — Invoices over a threshold
When the criteria range and the sum range are the same, you can omit the third argument entirely.
| A Invoice | B Amount | C Total > 1000 | |
|---|---|---|---|
| 2 | INV-1042 | 1250 | =SUMIF(B2:B6,">1000") |
| 3 | INV-1039 | 3400 | |
| 4 | INV-1035 | 820 | |
| 5 | INV-1028 | 5600 | |
| 6 | INV-1021 | 450 | 10250 |
=SUMIF(B2:B6,">1000") → 1250 + 3400 + 5600 = 10250Example 3 — Expenses by category
| A Date | B Category | C Amount | D Travel Total | |
|---|---|---|---|---|
| 2 | 2026-01-05 | Travel | 420 | =SUMIF(B2:B6,"Travel",C2:C6) |
| 3 | 2026-01-08 | Software | 199 | |
| 4 | 2026-01-12 | Travel | 380 | |
| 5 | 2026-01-15 | Office | 150 | |
| 6 | 2026-01-20 | Travel | 510 | 1310 |
=SUMIF(B2:B6,"Travel",C2:C6) → 420 + 380 + 510 = 1310Example 4 — Dates on or after a start date
Date criteria need two things: a real date value, and the operator joined to it with &.
' Sum amounts for dates on or after 1 Jan 2026
=SUMIF(A2:A20,">="&DATE(2026,1,1),B2:B20)
' If the start date is in cell E2
=SUMIF(A2:A20,">="&E2,B2:B20)
If your dates are stored as text, SUMIF often returns 0. Convert them with DATEVALUE or re-enter them as real dates.
Example 5 — Partial matches with wildcards
' Every region starting with "North"
=SUMIF(A2:A20,"North*",B2:B20)
' Every three-character code ending in C
=SUMIF(A2:A20,"??C",B2:B20)
' A literal asterisk in the text
=SUMIF(A2:A20,"~*",B2:B20)
SUMIF vs SUMIFS
SUMIF handles one condition. The moment you need two or more conditions, switch to SUMIFS. The two look similar but the argument order is different, and that is where most people slip up.
=SUMIF(A2:A20,"East",B2:B20)=SUMIFS(B2:B20,
A2:A20,"East",
C2:C20,">1000")| Function | Best for | Argument order |
|---|---|---|
SUMIF | Exactly one condition | range, criteria, [sum_range] |
SUMIFS | Two or more conditions | sum_range, criteria_range1, criteria1, … |
' Sum sales for East over 1000
=SUMIFS(B2:B20,A2:A20,"East",B2:B20,">1000")
' Sum between two dates
=SUMIFS(B2:B20,
A2:A20,">="&DATE(2026,1,1),
A2:A20,"<="&DATE(2026,12,31))
' Sum by region and category
=SUMIFS(D2:D100,B2:B100,"East",C2:C100,"Travel")
If you are unsure which one to reach for, SUMIFS is the safer modern choice — it handles one condition just as well as many.
SUMIF with Dates — Monthly, Quarterly, and YTD Totals
Date-based totals are the single most common use of conditional sums in real reports. Sales by month, expenses by quarter, running totals for the year — they all come down to the same two patterns: a start date and an end date.
Because SUMIF only handles one condition, any date range (a month, a quarter, a year) needs either two SUMIFS criteria, or one SUMIF with a helper column that already contains the month. Both approaches are shown below.
The starting data
| A Date | B Region | C Amount | D Month | |
|---|---|---|---|---|
| 2 | 2026-01-05 | East | 1200 | 2026-01 |
| 3 | 2026-01-18 | West | 900 | 2026-01 |
| 4 | 2026-02-02 | East | 1500 | 2026-02 |
| 5 | 2026-02-19 | East | 800 | 2026-02 |
| 6 | 2026-03-08 | North | 700 | 2026-03 |
| 7 | 2026-03-22 | East | 1100 | 2026-03 |
=TEXT(A2,"yyyy-mm") or =EOMONTH(A2,0) when a real date is needed for grouping.Pattern 1 — Monthly total with SUMIFS (recommended)
- Pick the month start. Use
DATE(2026,1,1)for January 2026. - Pick the month end. Use
EOMONTH(DATE(2026,1,1),0). It returns 31 Jan 2026 without you needing to know how many days are in the month. - Combine both bounds with SUMIFS. One condition for
>= start, one for<= end.
' January 2026 total — all regions
=SUMIFS(C2:C7,
A2:A7,">="&DATE(2026,1,1),
A2:A7,"<="&EOMONTH(DATE(2026,1,1),0))
' January 2026 total — East only
=SUMIFS(C2:C7,
A2:A7,">="&DATE(2026,1,1),
A2:A7,"<="&EOMONTH(DATE(2026,1,1),0),
B2:B7,"East")
Pattern 2 — Monthly total with helper column and SUMIF
If you already built a Month column with =TEXT(A2,"yyyy-mm"), a plain SUMIF is enough. This is the fastest pattern for large workbooks because the text comparison is cheap.
' Column D contains "2026-01", "2026-02", ...
=SUMIF(D2:D7,"2026-01",C2:C7)
' Or reference the label from a cell (E2 = "2026-01")
=SUMIF(D2:D7,E2,C2:C7)
Pattern 3 — Quarterly total
A quarter is just a wider date range. Q1 runs from 1 Jan to 31 Mar. Use DATE() for the boundaries so Excel never misreads a text date.
' Q1 2026 (Jan 1 – Mar 31)
=SUMIFS(C2:C7,
A2:A7,">="&DATE(2026,1,1),
A2:A7,"<="&DATE(2026,3,31))
' Q2 2026 (Apr 1 – Jun 30)
=SUMIFS(C2:C7,
A2:A7,">="&DATE(2026,4,1),
A2:A7,"<="&DATE(2026,6,30))
Pattern 4 — Year-to-date (YTD)
YTD is a single-sided range: everything from 1 Jan of the current year up to today. One SUMIFS condition is enough.
' YTD using TODAY() — always current
=SUMIFS(C2:C7,
A2:A7,">="&DATE(YEAR(TODAY()),1,1),
A2:A7,"<="&TODAY())
' YTD for a fixed year (2026)
=SUMIFS(C2:C7,
A2:A7,">="&DATE(2026,1,1),
A2:A7,"<="&DATE(2026,12,31))
Instead of typing DATE(2026,1,1) and DATE(2026,1,31), put the month start in one cell and use =EOMONTH(E2,0) for the end. Change the start and the whole report follows.
If column A shows dates but they are stored as text (left-aligned in the cell, no date formatting), >=DATE(...) will never match, and the formula quietly returns 0. Verify with =ISNUMBER(A2) — a real date returns TRUE.
SUMIF Across Multiple Sheets and Workbooks
Most real workbooks are not one giant table. They are one sheet per month, one sheet per region, or one file per department. SUMIF can reach across all of them — but the syntax changes slightly, and there are two traps worth knowing about.
Referencing another sheet
Prefix the range with the sheet name and an exclamation mark: SheetName!A2:A100. If the sheet name has spaces, wrap it in single quotes: 'Jan 2026'!A2:A100.
' Sum column B on the sheet named Jan where column A = "East"
=SUMIF(Jan!A2:A100,"East",Jan!B2:B100)
' Sheet name with a space needs single quotes
=SUMIF('Jan 2026'!A2:A100,"East",'Jan 2026'!B2:B100)
Combining several sheets into one total
SUMIF does not accept a true 3D reference like Jan:Dec!A2:A100. The reliable pattern is to add one SUMIF per sheet.
' East sales across January, February, and March
=SUMIF(Jan!A:A,"East",Jan!B:B)
+SUMIF(Feb!A:A,"East",Feb!B:B)
+SUMIF(Mar!A:A,"East",Mar!B:B)
Every Jan!A:A scans the entire column, even the million empty rows. If you have ten sheets and ten rows of formulas, that is a thousand full-column scans and Excel will feel it. Use bounded ranges like Jan!A2:A1000, or Excel Tables with structured references.
Referencing a sheet whose name is in a cell
If you want the sheet name to be dynamic — for example a drop-down in cell E1 that switches between Jan, Feb, and Mar — use INDIRECT.
' E1 contains the sheet name, e.g. "Feb"
=SUMIF(INDIRECT(E1&"!A2:A100"),"East",
INDIRECT(E1&"!B2:B100"))
Volatile means it recalculates on every change anywhere in the workbook, which slows large files down. It also does not survive a sheet rename — the formula just breaks. Use it sparingly and document it.
Referencing another workbook
Same idea, but with the file name in square brackets. Closed workbooks must be referenced with the full path.
' Referencing an open workbook
=SUMIF([Sales.xlsx]Jan!A:A,"East",[Sales.xlsx]Jan!B:B)
' Referencing a closed workbook on disk
=SUMIF('C:\Reports\[Sales.xlsx]Jan'!A:A,"East",
'C:\Reports\[Sales.xlsx]Jan'!B:B)
=SUMIF(Jan!A2:A1000,"East",Jan!B2:B1000)
+SUMIF(Feb!A2:A1000,"East",Feb!B2:B1000)=SUMIF(INDIRECT(E1&"!A:A"),"East",
INDIRECT(E1&"!B:B"))SUMIF vs SUMPRODUCT vs FILTER vs PivotTable — Choosing the Right Tool
SUMIF is excellent at one thing: a fixed conditional total in a specific cell. It is the wrong tool for several other jobs, and knowing when to switch saves hours of workarounds.
| Tool | Best for | Watch out for |
|---|---|---|
SUMIF | One condition, one fixed total | No support for AND/OR inside a single condition — use SUMIFS or SUMPRODUCT. |
SUMIFS | Two or more conditions on separate columns | Argument order is inverted compared to SUMIF. |
SUMPRODUCT | OR logic, case-sensitive matching, or conditions on calculated values | Array-based and heavier on large ranges; syntax is less forgiving. |
FILTER + SUM | Spilling a filtered table, then summing it | Microsoft 365 and Excel 2021+ only. |
PivotTable | Exploring totals from many angles interactively | Needs a refresh to pick up new data; not a live cell value. |
Same question, four tools
“What was East's total for amounts over 1000?” — a single condition plus a numeric filter. Here is how each tool answers it.
' 1. SUMIFS — the normal answer
=SUMIFS(B2:B100, A2:A100,"East", B2:B100,">1000")
' 2. SUMPRODUCT — needed when logic goes beyond two columns
=SUMPRODUCT((A2:A100="East")*(B2:B100>1000)*B2:B100)
' 3. FILTER + SUM — reads like a sentence (M365 / 2021)
=SUM(FILTER(B2:B100, (A2:A100="East")*(B2:B100>1000)))
' 4. PivotTable — drag Region to Rows, Amount to Values,
' and set a Value Filter of "greater than 1000"
When SUMIF is the wrong choice
“East OR West”
SUMIF cannot express OR inside a single condition. Add two SUMIFs, or switch to SUMPRODUCT with a + between conditions.
“Match exactly 'EAST'”
SUMIF is case-insensitive. Use SUMPRODUCT(--EXACT(A2:A100,"EAST"),B2:B100).
“Where Month(sale) = 3”
SUMIF cannot test a formula against a range. Add a helper column with the month, or use SUMPRODUCT with MONTH() on the range.
“Show me everything for East”
If the answer is a list, not a single number, use FILTER (M365) or a PivotTable — not SUMIF.
One condition → SUMIF. Two or more → SUMIFS. OR logic, case sensitivity, or formula-based conditions → SUMPRODUCT. A whole filtered list → FILTER or PivotTable.
Build a Sales Dashboard with SUMIF
Everything up to this point comes together here: one raw data sheet, one summary sheet, and a handful of SUMIF and SUMIFS formulas that update automatically when new rows arrive.
Step 1 — Lay out the source data as a Table
Select the data, then press Ctrl+T. Tables give you automatic named ranges that grow when you add rows, so your dashboard never needs to be edited again.
| A Date | B Region | C Rep | D Amount | |
|---|---|---|---|---|
| 2 | 2026-01-05 | East | Ana | 1200 |
| 3 | 2026-01-18 | West | Ben | 900 |
| 4 | 2026-02-02 | East | Carla | 1500 |
| 5 | 2026-02-19 | East | Ana | 800 |
| 6 | 2026-03-08 | North | Ben | 700 |
| 7 | 2026-03-22 | East | Carla | 1100 |
SalesData. All formulas below use SalesData instead of cell ranges.Step 2 — Create a summary sheet with the headings
On a new sheet, build this small grid. The Region and Month cells will hold the criteria; the values come from SUMIFS.
| A Region | B Q1 Total | C YTD Total | D Orders | |
|---|---|---|---|---|
| 2 | East | 4600 | 4600 | 4 |
| 3 | West | 900 | 900 | 1 |
| 4 | North | 700 | 700 | 1 |
| 5 | South | 0 | 0 | 0 |
Step 3 — Write the formulas
- Q1 Total (cell B2): two date bounds plus the region test.
=SUMIFS(SalesData[Amount], SalesData[Date], ">="&DATE(2026,1,1), SalesData[Date], "<="&DATE(2026,3,31), SalesData[Region], $A2) - YTD Total (cell C2): one-sided date range from Jan 1 to today.
=SUMIFS(SalesData[Amount], SalesData[Date], ">="&DATE(YEAR(TODAY()),1,1), SalesData[Date], "<="&TODAY(), SalesData[Region], $A2) - Orders (cell D2): same shape, but count instead of sum.
=COUNTIFS(SalesData[Region], $A2, SalesData[Date], ">="&DATE(2026,1,1)) - Copy B2:D2 down to row 5. The
$A2reference is locked to column A but free in the row, so each region picks up its own total.
Step 4 — Add a grand total that never needs updating
Because the source is a Table, the totals stay correct as rows are added. The dashboard row is simply a summary of the summary.
' Total sales across all regions
=SUM(SalesData[Amount])
' Total only for Q1
=SUMIFS(SalesData[Amount],
SalesData[Date],">="&DATE(2026,1,1),
SalesData[Date],"<="&DATE(2026,3,31))
' East's share of Q1 (percentage)
=B2 / SUM(SalesData[Amount])
Three decisions make the dashboard durable: the source is a Table (grows automatically), every criterion lives in a cell (editable data, not hidden in a formula), and every date bound is built with DATE() (immune to regional date formats).
If you rename SalesData to Sales, every formula breaks with #REF!. Keep the name stable, or use Find & Replace to update all at once.
Common Errors and How to Fix Them
Most SUMIF problems fall into one of a handful of patterns. Each one below has a known cause and a known fix.
You wrote =SUMIF(A2:A10,East,B2:B10). Excel looks for a defined name called East and finds nothing.
Fix: wrap text criteria in double quotes: "East".
The criteria string is invalid, or a cell inside sum_range contains an error value.
Fix: check quotes and operators. Clean the error cells in sum_range, or wrap SUMIF in IFERROR.
Four common causes: no row actually matches the criteria, numbers stored as text, dates stored as text, or extra spaces around text values.
Fix: clean the data with TRIM and VALUE, or re-enter the values in the correct type.
range and sum_range have different heights. Excel resizes from the top-left cell, which can add the wrong rows.
Fix: make both ranges the same number of rows.
Your data contains a literal * or ? and Excel treats it as a wildcard.
Fix: escape it with a tilde, for example "~*".
SUMIF is not case-sensitive. To sum only exact-case matches, use SUMPRODUCT with EXACT.
' SUMIF treats "East" and "east" the same
=SUMIF(A2:A20,"East",B2:B20)
' Case-sensitive sum
=SUMPRODUCT(--EXACT(A2:A20,"East"),B2:B20)
Best Practices for SUMIF
These habits keep conditional totals accurate, fast, and easy to audit months later.
Use Excel Tables
Structured references grow automatically and make formulas easier to read.
Put criteria in cells
A criterion buried in a formula is invisible. A cell reference is editable data.
Match range sizes
Keep range and sum_range the same height to avoid silent errors.
Prefer SUMIFS for two or more
SUMIFS is clearer than stacking SUMIFs or working around with arrays.
Use real dates
Text dates are one of the top causes of zero totals. Convert them to real date values.
Escape wildcards when needed
Use ~* and ~? to match literal wildcard characters in your data.
Check for text numbers
Numbers stored as text are ignored. Convert with VALUE or Text to Columns.
Document thresholds
If you use ">1000", note why 1000 matters. Future you will thank you.
Bound your ranges
Prefer A2:A10000 over A:A on multi-sheet formulas for speed.
Avoid INDIRECT unless you must
It is volatile and breaks on sheet renames. Prefer direct references.
Practice Questions
Try these before opening the answers. They use the examples from earlier in the guide.
- Q1. What does
=SUMIF(A2:A10,"East",B2:B10)do?
Answer: It scans A2:A10 for the text “East” and adds the corresponding values from B2:B10. - Q2. How do you write a criteria for “greater than 1000”?
Answer:">1000"— the operator and number go inside double quotes. - Q3. What does the wildcard
"North*"match?
Answer: Any text starting with “North”, such as North, Northeast, and Northwest. - Q4. What is the argument order for SUMIFS?
Answer:=SUMIFS(sum_range, criteria_range1, criteria1, …). The sum range comes first. - Q5. Write a formula that totals column C for January 2026 using the date in column A.
Answer:=SUMIFS(C2:C100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<="&EOMONTH(DATE(2026,1,1),0)). - Q6. Why is
INDIRECTdiscouraged in large workbooks?
Answer: It is volatile — it recalculates on every change anywhere in the workbook — and it breaks when a referenced sheet is renamed.
Frequently Asked Questions
What is the SUMIF function in Excel?
SUMIF adds the values in a range that meet a single condition. Its syntax is =SUMIF(range, criteria, [sum_range]).
How many arguments does SUMIF take?
Three: range (required), criteria (required), and sum_range (optional). If you omit sum_range, Excel sums the range itself.
What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition. SUMIFS handles multiple conditions and puts sum_range first: =SUMIFS(sum_range, criteria_range1, criteria1, ...).
Can SUMIF use wildcards?
Yes. Use * for any number of characters, ? for a single character, and ~ to escape a wildcard.
Is SUMIF case-sensitive?
No. SUMIF is not case-sensitive. For case-sensitive sums, use SUMPRODUCT with EXACT.
How do I sum values by month with SUMIF?
Use SUMIFS with two date criteria: =SUMIFS(C2:C100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<="&EOMONTH(DATE(2026,1,1),0)).
Can SUMIF reference another worksheet?
Yes. Prefix both ranges with the sheet name: =SUMIF(Jan!A:A,"East",Jan!B:B). To combine sheets, add multiple SUMIF formulas together.
When should I use SUMIF instead of a PivotTable?
Use SUMIF when you need a fixed total in a specific cell. Use a PivotTable when you need to slice the data many different ways interactively.
Why does my SUMIF return 0?
Common causes include no matching criteria, numbers stored as text, dates stored as text, or extra spaces in text values.
Does SUMIF work with text and numbers?
SUMIF sums numeric values only. It can match text criteria, but the sum_range should contain numbers.
Key Takeaways
- SUMIF adds values that meet one condition.
- The syntax is
=SUMIF(range, criteria, [sum_range]). - If you omit
sum_range, SUMIF sums the criteria range. - Text and operators in criteria must be in double quotes.
- Use
&to combine an operator with a cell reference. - Wildcards
*and?work in text criteria; escape them with~. - SUMIF is not case-sensitive.
- Use SUMIFS when you need two or more conditions.
- For monthly, quarterly, or YTD totals, pair
DATE()withEOMONTH(). - Cross-sheet SUMIF uses
SheetName!Range; add formulas together to combine sheets. - Reach for SUMPRODUCT or FILTER when the logic is beyond a single condition.
- Build dashboards on Excel Tables so ranges grow automatically.
- Keep
rangeandsum_rangethe same size. - For case-sensitive sums, use
SUMPRODUCTwithEXACT.
What to Learn Next
SUMIF is the gateway to Excel's conditional calculation family. Once it is comfortable, the natural next steps are the functions that handle multiple conditions, date ranges, and dynamic arrays.
Start with the functions that pair with SUMIF — sumifs-function, countif-function, and averageif-function.
From there, move into multi-condition summaries and modern dynamic tools: countifs-function, sumproduct-function, filter-function, and xlookup-function.