How to Use SUMIF and SUMIFS in Excel (Step-by-Step)

By Vinai Prakash · 12 min read · 10 June 2026

How to Use SUMIF and SUMIFS in Excel (Step-by-Step)

TL;DR

To use SUMIF in Excel, write =SUMIF(check_range, criterion, [add_range]). For example, =SUMIF(B2:B100, "APAC", D2:D100) totals revenue where the region is APAC. To sum on two or more conditions, switch to SUMIFS: =SUMIFS(total_range, range_a, condition_a, range_b, condition_b, …). The argument order flips, and the sum range moves to the front. Both functions accept numbers, text, and dates as criteria.

This guide walks through both functions the way I teach them in our Excel classroom — the syntax first, then worked examples, then the SUMIF-versus-SUMIFS decision, three Singapore business scenarios, and the five reasons your formula returns zero when you expected a real number. After 24 years of training in Singapore, I can tell you the same five things trip people up every time. By the end of this article, you will know which function to reach for, how to write it correctly, and how to diagnose it when it does not behave.

What SUM does (and when you need to upgrade to SUMIF)

=SUM(A2:A100) adds every numeric value in the range. It is the first formula most people learn, and the foundation the rest of the sum family extends.

=SUM(D2:D100)

This returns the total of every number in D2:D100. Blank cells and text cells are ignored. Negative numbers are subtracted, because that is what adding a negative does.

Think of SUM as your office calculator. It adds the column. Job done. But the moment your boss says, “Give me the total only for the APAC region,” the calculator is no longer enough. That is the trigger for SUMIF.

Three signs you have outgrown SUM:

• You catch yourself filtering the data first, then totalling the visible rows. SUMIF does both in one cell.
• You write =SUM(D2:D100) - SUM(D2:D50) to get a partial sum. SUMIF is the cleaner way.
• You pick out matching rows by eye and add them. SUMIF is the function for exactly that job.

SUM gives you the unconditional total. SUMIF gives you the same total filtered by one condition. SUMIFS gives you the total filtered by many. Once you see that ladder, choosing the right function takes two seconds.

How to use SUMIF in Excel (single-criterion sum)

The SUMIF syntax is:

=SUMIF(range, criteria, [sum_range])

Three arguments:

• range — the cells Excel checks against your condition.
• criteria — the condition that must be met. A number, a text string, a comparison expression, a cell reference, or a date.
• sum_range — the cells to add when the condition is met. Optional. Leave it out and SUMIF adds the values in the first range argument itself.

Think of the criteria as a sieve. The range is the pile of data. The criteria sieves out the rows you do not want. The sum_range is the column whose values you add up after the sieve has done its work. Simple enough.

SUMIF with a number criterion

To total only the sales above $225 in column C:

=SUMIF(C2:C100, ">225")

Notice the sum_range was left off. Because the range we are checking is the same as the range we want to sum, Excel sums the range argument. The comparison operator ">225" is wrapped in double quotes. Without the quotes, Excel reads it as a malformed expression. That single missing quote is a classroom favourite — half the room misses it on day one.

SUMIF with a text criterion

To total only the sales in the “Singapore” region (region in column B, sales in column D):

=SUMIF(B2:B100, "Singapore", D2:D100)

SUMIF is not case-sensitive. "singapore", "SINGAPORE", and "Singapore" all match the same cells. Useful when your data has been typed by half a dozen people who each had their own idea of capitalisation.

SUMIF with a date criterion

To total the sales on a specific date (dates in column A, sales in column D):

=SUMIF(A2:A100, DATE(2024,10,31), D2:D100)

Use the DATE() helper rather than typing "31/10/2024" as text. Singapore Excel uses DD/MM/YYYY. US Excel uses MM/DD/YYYY. A hard-coded text date will fail on the wrong locale, and you will not see it until your colleague in another office opens the file. DATE(year, month, day) produces the same underlying value on every machine.

For “before a date”, use the less-than operator: =SUMIF(A2:A100, "<"&DATE(2024,10,31), D2:D100). The ampersand joins the operator string "<" to the date value. The ampersand here works like super glue — it sticks the two pieces into one criterion that Excel can read.

SUMIF with wildcards

SUMIF supports two wildcard characters in the criteria:

• ? stands in for exactly one character — useful for fixed-length codes.
• * stands in for any run of characters (zero or more) — useful for prefix or suffix matches.

To total sales for any product code starting with “ABC”:

=SUMIF(C2:C100, "ABC*", D2:D100)

To total sales for product codes ending in “00”:

=SUMIF(C2:C100, "*00", D2:D100)

Wildcards work in SUMIFS the same way. They are extremely useful when product codes have a structure (prefix = product family, suffix = variant) and you want to total by structure rather than by exact code.

How to use SUMIFS in Excel (multiple criteria)

The SUMIFS syntax is:

=SUMIFS(total_range, range_a, condition_a, [range_b, condition_b], …)

Two structural differences from SUMIF:

1. The sum range comes first, and it is required. SUMIF puts it last and treats it as optional.
2. You can pass up to 127 criteria-range / criteria pairs. All conditions must be met for a row to be summed. SUMIFS uses AND logic.

This argument-order flip is one of the most-asked questions in our Excel classes. Microsoft did not do it to confuse you. SUMIFS was added later (in Excel 2007), and the team putting the sum range first lets you read the formula as “sum THIS, when these conditions hold.” Once you have written ten SUMIFS, the order becomes second nature. Microsoft’s official SUMIFS reference documents the full argument set if you ever need to look up an edge case.

Basic SUMIFS — two conditions

A table with region (column B), product (column C), and sales (column D). To total the sales of “Premium” products in the “Singapore” region:

=SUMIFS(D2:D100, B2:B100, "Singapore", C2:C100, "Premium")

Read it left to right: sum D2:D100, where B2:B100 equals “Singapore” AND C2:C100 equals “Premium”. The AND is silent but it is the rule.

SUMIFS with comparison operators

To total sales of $500 or more in the Singapore region:

=SUMIFS(D2:D100, B2:B100, "Singapore", D2:D100, ">=500")

The sum range and one of the criteria ranges can be the same column. Excel handles this without complaint.

To total sales between $500 and $2,000:

=SUMIFS(D2:D100, D2:D100, ">=500", D2:D100, "<=2000")

SUMIFS with a date range

To total sales for the month of October 2024:

=SUMIFS(D2:D100, A2:A100, ">="&DATE(2024,10,1), A2:A100, "<="&DATE(2024,10,31))

For a rolling last-7-days window, useful for a dashboard that should update on its own every morning:

=SUMIFS(D2:D100, A2:A100, ">="&TODAY()-7, A2:A100, "<="&TODAY())

TODAY() recalculates each time the file opens, so the rolling window stays current. Your boss opens the file on Monday and sees the previous Monday-to-Sunday total. Open it on Wednesday and the window has shifted. No manual updating.

SUMIFS with blank and non-blank cells

To total sales where the customer name column is filled in (column F):

=SUMIFS(D2:D100, F2:F100, "<>")

To total sales where the customer name is blank:

=SUMIFS(D2:D100, F2:F100, "")

This is the cleanest way to spot orphan transactions that need someone’s attention before month-end close. Run both. The two totals should add up to your overall sales figure. If they do not, you have rows with something neither blank nor a real name — usually a stray space, sometimes a stray apostrophe — and that needs cleaning up.

SUMIF vs SUMIFS — which to use when

The decision is rarely about capability. SUMIFS can do everything SUMIF can do. Use SUMIFS with one criterion and it behaves like SUMIF. The decision is about which one reads more clearly in your sheet, and which version of Excel your readers run.

The rule I give my classroom: one condition and a quick ad-hoc total, SUMIF is shorter to type. Two or more conditions, or a formula that will live in a model others have to read and audit, use SUMIFS even for a single condition. The argument order is consistent and the row-and-column rule keeps you from getting away with a sloppy reference that SUMIF would let pass. Discipline now, fewer reconciliation headaches later.

Real-world examples: Singapore business scenarios

Abstract examples are clean but hard to remember. Three SG-flavoured ones to anchor the formulas.

Monthly SGD sales by region

A sales register with date (A), region (B), salesperson (C), and amount in SGD (D). To get the October 2024 total for the APAC South region:

=SUMIFS(D2:D5000,
        A2:A5000, ">="&DATE(2024,10,1),
        A2:A5000, "<="&DATE(2024,10,31),
        B2:B5000, "APAC South")

Drag this down a small grid — one row per region, one column per month — and you have a monthly regional cube without touching a pivot table. Useful when the data updates daily and you want the cube to refresh on its own. Managers can be quite demanding. They want to see this month versus last month versus same month last year, and they want it before lunch. SUMIFS gives them all three before they have finished their first coffee.

GST split — taxable versus non-taxable revenue

A revenue line that mixes GST-taxable and non-taxable items, with the tax flag in column F ("Y" or "N") and the amount in column E:

Taxable revenue:     =SUMIF(F2:F1000, "Y", E2:E1000)
Non-taxable revenue: =SUMIF(F2:F1000, "N", E2:E1000)
Total GST collected: =SUMIF(F2:F1000, "Y", E2:E1000) * 9%

GST in Singapore is 9% from 2024. The taxable subtotal is the base for the GST return. The non-taxable subtotal is the reconciliation against the total revenue line in your management accounts. Two formulas, one report, no manual filtering. The accounts team will be delighted.

Training attendance by course code

A WSQ training register with course code (B), session date (C), and attendees (D). The training manager wants the head-count for a specific course code in a specific quarter:

=SUMIFS(D2:D2000,
        B2:B2000, "TGS-2024041234",
        C2:C2000, ">="&DATE(2024,7,1),
        C2:C2000, "<="&DATE(2024,9,30))

Change the course code and the date range, and the same formula serves any course, any quarter. For HR teams that run dozens of WSQ courses against the same register, this is what turns a thousand-row attendance log into a quarterly funding-claim total in about 30 seconds.

For a structured walk-through of these reporting patterns and the dozens of related techniques, our Excel Training in Singapore course covers them across two days of hands-on practice.

Why your SUMIF or SUMIFS isn’t working — 5 common failures

When a SUMIF or SUMIFS returns 0, #VALUE!, or a wrong total, the cause is almost always one of these five. After 24 years of teaching Excel in Singapore I see the same five patterns. Working through them in order will catch about 95% of broken formulas.

1. Trailing spaces or typos in the criteria

"APAC " (with a trailing space) is not the same string as "APAC". CSV imports and copy-pastes from PDFs frequently introduce trailing spaces. Test by typing the criterion as a hard-coded string first, then switching back to the cell reference. If the hard-coded version works and the cell-reference version does not, the cell has a hidden space.

The fix: =TRIM(F2) in a helper column to strip spaces, then use the helper column as the criterion.

2. Numbers stored as text

After importing from a CSV or a banking export, numeric columns sometimes arrive as text. Excel shows a small green triangle in the corner of these cells. SUMIF will skip them, because they are text and not numbers, and the total will be lower than expected. Often zero, if every cell is text.

The fix: select the column, click the warning triangle, choose “Convert to Number”. Or use =VALUE() in a helper column. Or, for repeated imports, set the column type during the import dialog so the problem never appears in the first place. Once your data is clean, you can pair these formulas with a filtered view to spot the rogue rows before they break a SUMIF.

3. Comparison operator not in quotes

=SUMIF(D2:D100, >100) returns an error. The comparison expression must be a quoted string: =SUMIF(D2:D100, ">100"). When the comparison value lives in a cell, use the ampersand to join: =SUMIF(D2:D100, ">"&G2). The ampersand is the super glue again — it sticks the operator and the value into one criterion.

4. Sum range and criteria range start on different rows

=SUMIF(B2:B100, "Singapore", D5:D100) will quietly return the wrong total. SUMIF does not error. It shifts the sum range three rows down and adds the wrong cells. The result looks plausible but is silently wrong. This is the most dangerous of the five, because nothing alerts you.

The fix: keep your ranges aligned. B2:B100 should be paired with D2:D100, not D5:D100. If you work with named ranges, this is one of the strongest reasons to use them. Named ranges enforce alignment by definition.

5. Mismatched range sizes in SUMIFS

=SUMIFS(D2:D100, B2:B100, "Singapore", C2:C50, "Premium") returns #VALUE!. The two criteria ranges have different row counts (100 versus 50), and SUMIFS requires every range to be identical in shape to the sum range. Fix: extend C2:C50 to C2:C100.

If you have walked through all five options, and the formula still misbehaves, the sixth thing to check is whether the function is the right one at all. Sometimes the right answer is a pivot table, not a SUMIFS. For roles where this kind of conditional reporting is daily work — payroll, headcount, training records — our Excel for HR professionals course works through the same patterns applied to real HR data.

Beyond SUMIFS — SUMPRODUCT, FILTER, and when to reach for them

SUMIFS handles roughly 90% of conditional-sum work. The other 10% needs a different tool.

SUMPRODUCT — when you need OR logic or case-sensitivity

SUMIFS uses AND logic. For OR across many values, you can stack SUMIFS, but the formula gets long fast. SUMPRODUCT with a boolean array is cleaner:

=SUMPRODUCT((B2:B100={"Singapore","Malaysia","Indonesia"})*D2:D100)

This sums D2:D100 where B2:B100 matches any of the three countries. The * between the boolean array and the value array is what makes SUMPRODUCT act like a conditional sum.

For case-sensitive criteria — when "APAC" must not match "apac" — SUMIFS cannot help, because it is case-insensitive. SUMPRODUCT with EXACT() does the job:

=SUMPRODUCT(--EXACT(B2:B100, "APAC"), D2:D100)

The double-minus (--) coerces TRUE/FALSE into 1/0 so SUMPRODUCT can multiply.

FILTER — Excel 365 only, but worth knowing

In Excel 365 and Excel 2021, FILTER() returns a dynamic array of matching rows that you can wrap in SUM:

=SUM(FILTER(D2:D100, (B2:B100="Singapore")*(C2:C100="Premium")))

This does the same thing as =SUMIFS(D2:D100, B2:B100, "Singapore", C2:C100, "Premium") but with one important difference. FILTER returns the actual matching rows, which you can also display. SUMIFS only returns the total.

When you need both the rows and the total — say, the matching transactions plus the subtotal at the bottom — FILTER is the cleaner pattern. For complex multi-criteria reporting at scale, advanced data analytics with Excel covers FILTER alongside the rest of the dynamic-array family. If you regularly chain FILTER with a lookup, our companion guide on VLOOKUP and XLOOKUP shows how to pair them.

Related Excel sum-family shortcuts

A few productivity wins that pair with SUMIF and SUMIFS.

Alt+= for AutoSum. Select an empty cell below or to the right of a range of numbers and press Alt+= (Alt and the equals sign together). Excel inserts =SUM() with what it guesses is the right range. Press Enter. There is no keyboard shortcut for SUMIF or SUMIFS, but Alt+= covers the unconditional case, which is the most common one by far. How good is that?

Named ranges. Instead of writing =SUMIFS(D2:D5000, B2:B5000, "Singapore"), name the ranges Sales and Region (Formulas → Define Name) and write =SUMIFS(Sales, Region, "Singapore"). The formula now reads like English. When the data grows past row 5000, you update the name once instead of editing every formula. Think of a named range as a label on a drawer. The drawer can hold 100 files today and 5,000 files next year, but the label stays the same.

Structured table references. If you have converted your data to an Excel Table (Ctrl+T), you can reference columns by name: =SUMIFS(Sales[Amount], Sales[Region], "Singapore"). The table auto-expands as you add rows, so the formula keeps working without manual range updates. This is the modern Excel best practice for any sheet that will grow over time.

COUNT and COUNTIF as parallels. SUMIF totals; COUNTIF counts the matching rows. They take the same arguments. =COUNTIF(B2:B100, "Singapore") returns the number of Singapore rows; =SUMIF(B2:B100, "Singapore", D2:D100) returns their total sales. Run both as a sanity check. If SUMIF returns a suspiciously low total, run a COUNTIF on the same criterion to confirm you have the row count you expect. The two together tell you the story — total and head-count, side by side.

For modelling work that goes deeper into these patterns — Power Query for the data loading, Power Pivot for cross-table SUMIFS-equivalents — have a look at Power Query and Power Pivot for deeper analysis.

FAQ

What is the difference between SUMIF and SUMIFS in Excel?

SUMIF sums values that meet one condition. SUMIFS sums values that meet two or more, joined by AND logic. The other big difference is the argument order. SUMIF puts the sum range last and treats it as optional: =SUMIF(range, criteria, [sum_range]). SUMIFS puts the sum range first and requires it: =SUMIFS(sum_range, criteria_range1, criteria1, …). SUMIFS arrived in Excel 2007 and is in every version since; SUMIF works everywhere. One condition, SUMIF is enough. Two or more, switch to SUMIFS.

Can SUMIF handle multiple criteria?

Not on its own. SUMIF is a one-condition function. You have three workarounds. First, add several SUMIF results together: =SUMIF(A:A, "Mike", B:B) + SUMIF(A:A, "John", B:B). Second, wrap SUMIF in SUM with an array criterion: =SUM(SUMIF(A:A, {"Mike","John"}, B:B)). Third, just switch to SUMIFS, which is built for it. For two or more permanent conditions in a reporting sheet, SUMIFS is cleaner. SUMIF with workarounds is fine for a quick ad-hoc sum across two or three named values.

Why is my SUMIF formula returning 0?

Five common causes. One, the criteria text has a trailing space — "APAC " does not match "APAC". Two, the numbers are stored as text after a CSV import. The green triangle in the cell corner is the giveaway. Three, the comparison operator is not in quotes — >100 will not work, but ">100" will. Four, the sum range is the wrong column. Five, the criteria range and sum range start on different rows, so SUMIF is comparing apples to oranges. Walk through these five before assuming the function is broken.

How do I use SUMIFS with a date range?

Use two date criteria, with the comparison operators wrapped in quotes and joined to the date with &. To sum sales between 1 October 2024 and 31 October 2024: =SUMIFS(C2:C100, B2:B100, ">="&DATE(2024,10,1), B2:B100, "<="&DATE(2024,10,31)). Using DATE() instead of typing the date as text protects you from regional differences (DD/MM/YYYY in Singapore versus MM/DD/YYYY in the US). For a rolling 7-day window, use ">="&TODAY()-7 and "<="&TODAY() as the two criteria.

Can SUMIF return a percentage of the total?

Yes. Divide a SUMIF (the conditional subtotal) by a SUM (the grand total). To find the share of revenue that came from APAC: =SUMIF(B2:B100, "APAC", D2:D100) / SUM(D2:D100). Format the result cell as Percentage (Ctrl+Shift+%). Do not multiply by 100 inside the formula. The percentage formatting does that for you, and if you do both you end up with 0.42% when you meant 42%. The same pattern works with SUMIFS divided by SUM when the numerator needs more than one condition.

Is there a keyboard shortcut for SUM in Excel?

Yes. Alt+= (Alt and the equals sign together) is the AutoSum shortcut. Select an empty cell below or to the right of a range of numbers, press Alt+=, and Excel inserts =SUM() with what it guesses is the right range already filled in. Press Enter to accept, or drag to adjust the range first. There is no equivalent shortcut for SUMIF or SUMIFS — those still need to be typed — but Alt+= covers the unconditional SUM case, which is the most common one by far.

That is the full SUMIF and SUMIFS playbook. The functions are not hard. The first five things that break them are the same first five things every time. I hope you will like this guide. Do try the formulas out on your own data this week — start with a SUMIF on one of your existing reports, then upgrade it to SUMIFS when you add a second condition. The fastest way to remember the argument order is to write three of each by hand, side by side. After that, your fingers know it.