SUMIF Function in Excel: Ultimate Guide

SUMIF Function in Excel: Ultimate Guide
IL Inar Learn
SUMIF Function in Excel — Ultimate Guide Saved
16 min read Excel 2016 · 2019 · 2021 · Microsoft 365 Updated for 2026

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.

In one sentence

SUMIF adds the values in a range that meet a single condition, written as =SUMIF(range, criteria, [sum_range]).

01

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.

FunctionSUMIF
CategoryMath & Trig
Arguments2–3
ReturnsNumber
SinceExcel 97+

How SUMIF evaluates a formula

Take the formula =SUMIF(A2:A10,"East",B2:B10). Excel does three things, in order:

  1. Scan the criteria range. It reads every cell in A2:A10 and asks: does this cell match “East”?
  2. Mark the matching rows. Every row where the test is TRUE gets flagged.
  3. Add the sum range. It adds the values in B2:B10 for those flagged rows only — and ignores everything else.
i
SUMIF is not case-sensitive

“East”, “east”, and “EAST” all match. If you need case-sensitive summing, use SUMPRODUCT with EXACT.

02

Syntax and Arguments

The full signature has three arguments. Only the last one is optional.

Formula syntax
=SUMIF(range, criteria, [sum_range])

Walking through each argument

  1. range — the cells you want to test. Usually a column of text, numbers, or dates.
  2. criteria — the condition to match. Can be a number, text, expression, cell reference, or function. Text and operators must be in double quotes.
  3. sum_range (optional) — the cells to add when the criteria match. If you omit it, Excel sums the range itself.
Argument reference
ArgumentRequired?Notes
rangeRequiredSame height as sum_range whenever possible.
criteriaRequiredText and operators in double quotes: "East", ">1000".
sum_rangeOptionalIf omitted, SUMIF adds the criteria range itself.
!
Text needs double quotes

=SUMIF(A2:A10,East,B2:B10) makes Excel look for a defined name called East. It fails with #NAME?. Use "East" instead.

03

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

Operators you can place inside SUMIF criteria
OperatorMeaningExample criteriaMatches 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 ?.

Criteria examples
' 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)
✓
Use & to combine an operator with a cell reference

If E2 contains 1000, write ">"&E2. Writing ">E2" looks for the literal text “>E2” and returns 0.

04

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

Sales by RegionSales.xlsx
A
Region
B
Sales
C
Total East
2East1200=SUMIF(A2:A6,"East",B2:B6)
3West900
4East1500
5North700
6East11003800
Formula: =SUMIF(A2:A6,"East",B2:B6) → 1200 + 1500 + 1100 = 3800

Example 2 — Invoices over a threshold

When the criteria range and the sum range are the same, you can omit the third argument entirely.

Invoice AmountsAR.xlsx
A
Invoice
B
Amount
C
Total > 1000
2INV-10421250=SUMIF(B2:B6,">1000")
3INV-10393400
4INV-1035820
5INV-10285600
6INV-102145010250
Formula: =SUMIF(B2:B6,">1000") → 1250 + 3400 + 5600 = 10250

Example 3 — Expenses by category

ExpensesExpenses.xlsx
A
Date
B
Category
C
Amount
D
Travel Total
22026-01-05Travel420=SUMIF(B2:B6,"Travel",C2:C6)
32026-01-08Software199
42026-01-12Travel380
52026-01-15Office150
62026-01-20Travel5101310
Formula: =SUMIF(B2:B6,"Travel",C2:C6) → 420 + 380 + 510 = 1310

Example 4 — Dates on or after a start date

Date criteria need two things: a real date value, and the operator joined to it with &.

Date criteria
' 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)
!
Dates must be real dates

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

Wildcard criteria
' 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)
05

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 — one condition
=SUMIF(A2:A20,"East",B2:B20)
Argument order: range, criteria, sum_range. Range comes first.
SUMIFS — multiple conditions
=SUMIFS(B2:B20, A2:A20,"East", C2:C20,">1000")
Argument order: sum_range first, then pairs of criteria_range and criteria.
Choosing between SUMIF and SUMIFS
FunctionBest forArgument order
SUMIFExactly one conditionrange, criteria, [sum_range]
SUMIFSTwo or more conditionssum_range, criteria_range1, criteria1, …
SUMIFS in practice
' 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")
✓
SUMIFS can do everything SUMIF can

If you are unsure which one to reach for, SUMIFS is the safer modern choice — it handles one condition just as well as many.

06

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

Transactions — 2026Transactions.xlsx
A
Date
B
Region
C
Amount
D
Month
22026-01-05East12002026-01
32026-01-18West9002026-01
42026-02-02East15002026-02
52026-02-19East8002026-02
62026-03-08North7002026-03
72026-03-22East11002026-03
Month column (D): calculated with =TEXT(A2,"yyyy-mm") or =EOMONTH(A2,0) when a real date is needed for grouping.

Pattern 1 — Monthly total with SUMIFS (recommended)

  1. Pick the month start. Use DATE(2026,1,1) for January 2026.
  2. 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.
  3. Combine both bounds with SUMIFS. One condition for >= start, one for <= end.
Monthly total
' 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.

SUMIF on a helper column
' 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.

Quarterly totals
' 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.

Year-to-date totals
' 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))
✓
Make month labels dynamic with EOMONTH

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.

✕
Text dates break SUMIF silently

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.

07

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.

Cross-sheet SUMIF
' 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.

Total across three sheets
' 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)
!
Whole-column references across sheets add up fast

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.

Dynamic sheet reference with INDIRECT
' E1 contains the sheet name, e.g. "Feb"
=SUMIF(INDIRECT(E1&"!A2:A100"),"East",
       INDIRECT(E1&"!B2:B100"))
✕
INDIRECT is volatile and will not track renames

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.

Cross-workbook SUMIF
' 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)
Works reliably
=SUMIF(Jan!A2:A1000,"East",Jan!B2:B1000) +SUMIF(Feb!A2:A1000,"East",Feb!B2:B1000)
Bounded ranges on each sheet, added together. Fast and rename-safe.
Works but risky
=SUMIF(INDIRECT(E1&"!A:A"),"East", INDIRECT(E1&"!B:B"))
Flexible sheet name, but volatile and breaks on rename. Use only when the flexibility is required.
08

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.

Which tool to reach for
ToolBest forWatch out for
SUMIFOne condition, one fixed totalNo support for AND/OR inside a single condition — use SUMIFS or SUMPRODUCT.
SUMIFSTwo or more conditions on separate columnsArgument order is inverted compared to SUMIF.
SUMPRODUCTOR logic, case-sensitive matching, or conditions on calculated valuesArray-based and heavier on large ranges; syntax is less forgiving.
FILTER + SUMSpilling a filtered table, then summing itMicrosoft 365 and Excel 2021+ only.
PivotTableExploring totals from many angles interactivelyNeeds 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.

Four ways to sum the same data
' 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

OR

“East OR West”

SUMIF cannot express OR inside a single condition. Add two SUMIFs, or switch to SUMPRODUCT with a + between conditions.

CASE

“Match exactly 'EAST'”

SUMIF is case-insensitive. Use SUMPRODUCT(--EXACT(A2:A100,"EAST"),B2:B100).

CALC

“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.

LIVE

“Show me everything for East”

If the answer is a list, not a single number, use FILTER (M365) or a PivotTable — not SUMIF.

i
Rule of thumb

One condition → SUMIF. Two or more → SUMIFS. OR logic, case sensitivity, or formula-based conditions → SUMPRODUCT. A whole filtered list → FILTER or PivotTable.

09

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.

Source — Table1Dashboard.xlsx
A
Date
B
Region
C
Rep
D
Amount
22026-01-05EastAna1200
32026-01-18WestBen900
42026-02-02EastCarla1500
52026-02-19EastAna800
62026-03-08NorthBen700
72026-03-22EastCarla1100
Table name: after creating the Table, change its name in the Table Design tab to 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.

Summary sheetDashboard.xlsx
A
Region
B
Q1 Total
C
YTD Total
D
Orders
2East460046004
3West9009001
4North7007001
5South000
Each cell in columns B, C and D is a single formula copied down.

Step 3 — Write the formulas

  1. 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)
  2. 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)
  3. Orders (cell D2): same shape, but count instead of sum.
    =COUNTIFS(SalesData[Region], $A2, SalesData[Date], ">="&DATE(2026,1,1))
  4. Copy B2:D2 down to row 5. The $A2 reference 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.

Dashboard footer formulas
' 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])
✓
Why this pattern stays reliable

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).

!
Structured references need the exact table name

If you rename SalesData to Sales, every formula breaks with #REF!. Keep the name stable, or use Find & Replace to update all at once.

10

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.

✕
#NAME? — unquoted text

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".

✕
#VALUE! — malformed criteria or broken sum range

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.

!
Returns 0 — nothing matched

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.

!
Wrong total — mismatched range sizes

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.

!
Wildcard not matching what you expect

Your data contains a literal * or ? and Excel treats it as a wildcard.

Fix: escape it with a tilde, for example "~*".

i
Case-sensitive match needed

SUMIF is not case-sensitive. To sum only exact-case matches, use SUMPRODUCT with EXACT.

Case-sensitive alternative
' SUMIF treats "East" and "east" the same
=SUMIF(A2:A20,"East",B2:B20)

' Case-sensitive sum
=SUMPRODUCT(--EXACT(A2:A20,"East"),B2:B20)
11

Best Practices for SUMIF

These habits keep conditional totals accurate, fast, and easy to audit months later.

01

Use Excel Tables

Structured references grow automatically and make formulas easier to read.

02

Put criteria in cells

A criterion buried in a formula is invisible. A cell reference is editable data.

03

Match range sizes

Keep range and sum_range the same height to avoid silent errors.

04

Prefer SUMIFS for two or more

SUMIFS is clearer than stacking SUMIFs or working around with arrays.

05

Use real dates

Text dates are one of the top causes of zero totals. Convert them to real date values.

06

Escape wildcards when needed

Use ~* and ~? to match literal wildcard characters in your data.

07

Check for text numbers

Numbers stored as text are ignored. Convert with VALUE or Text to Columns.

08

Document thresholds

If you use ">1000", note why 1000 matters. Future you will thank you.

09

Bound your ranges

Prefer A2:A10000 over A:A on multi-sheet formulas for speed.

10

Avoid INDIRECT unless you must

It is volatile and breaks on sheet renames. Prefer direct references.

12

Practice Questions

Try these before opening the answers. They use the examples from earlier in the guide.

  1. 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.
  2. Q2. How do you write a criteria for “greater than 1000”?
    Answer: ">1000" — the operator and number go inside double quotes.
  3. Q3. What does the wildcard "North*" match?
    Answer: Any text starting with “North”, such as North, Northeast, and Northwest.
  4. Q4. What is the argument order for SUMIFS?
    Answer: =SUMIFS(sum_range, criteria_range1, criteria1, …). The sum range comes first.
  5. 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)).
  6. Q6. Why is INDIRECT discouraged 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.
13

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.

14

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() with EOMONTH().
  • 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 range and sum_range the same size.
  • For case-sensitive sums, use SUMPRODUCT with EXACT.
15

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.

Inar Learn
Inar Learnhttps://inarlearn.com
Inar Learn is an innovative online learning platform offering high-quality courses, tutorials, and resources to help learners gain practical skills and grow their knowledge.

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here