SUMIF Function in Excel — Complete Guide (2026)

If you've ever needed to add up only certain rows in a spreadsheet — say, total sales for a specific region, or revenue from one product — the =SUMIF() function is exactly what you're looking for. It's one of Excel's most practical built-in functions, and once you understand how it works, you'll reach for it constantly.

At its core, SUMIF lets you sum a range of cells conditionally. Instead of adding everything up, you define a rule — a criterion — and Excel sums only the matching rows. That's the whole idea. The syntax looks like this:

=SUMIF(range, criteria, [sum_range])

Breaking that down: range is the column (or row) Excel checks against your condition. criteria is the condition itself — a number, text string, expression, cell reference, or wildcard pattern. sum_range is the column you actually want to add up. If you omit sum_range, Excel sums the range itself instead. That third argument is optional, but you'll almost always include it — the common pattern is checking one column and summing a different one.

Here's a quick mental model: think of range as 'which column do I check?' and sum_range as 'which column do I add?' Those two don't have to be the same. In real-world use, they usually aren't. You might check a Product column and sum a Revenue column, or check a Date column and sum an Hours column.

When should you use SUMIF rather than something else? Use it when you have one condition and want a sum. Need to count matching rows instead of summing values? That's countifs excel territory — same idea but for counting. Need two or more conditions at the same time? SUMIFS (plural) handles that — we'll cover the difference in detail later in this guide. And if your analysis is more exploratory — grouping, filtering, pivoting across multiple dimensions — you're probably better off with excel pivot tables rather than a formula approach.

One thing SUMIF does well that beginners often overlook: it works beautifully with entire columns like A:A and B:B. You don't need to lock down specific row ranges like A2:A500. Excel is smart enough to skip header rows when the criteria don't match, so =SUMIF(A:A,"Apple",B:B) just works without you counting rows first. This makes your formulas robust — they automatically pick up new rows as your data grows.

SUMIF is also case-insensitive by default. "apple", "Apple", and "APPLE" all match the same rows. That's usually what you want in a data entry scenario where capitalization is inconsistent. If you need case-sensitive matching, you'd have to switch to an array formula using SUMPRODUCT and EXACT — but that's an edge case most users never encounter.

Performance-wise, SUMIF is fast on reasonably sized datasets. On millions of rows it can slow down, but for typical business spreadsheets with tens of thousands of rows, it's nearly instant. Using whole-column references (A:A instead of A2:A1000) is slightly slower but more maintainable — a tradeoff most people choose without regret.

Throughout this guide you'll see every major use case: text matches, number comparisons, wildcards, date ranges, and the between-two-values technique. You'll also learn when to switch from SUMIF to SUMIFS, and what the most common mistakes look like so you can avoid them. Keep a copy of the broader excel formula reference handy as you follow along — it covers the full formula library if you need to cross-reference anything.

Basic SUMIF Examples Step by Step

Let's walk through the most common SUMIF patterns so you can see exactly how each argument maps to your spreadsheet. These aren't abstract — they're the formulas you'll actually type.

1. Sum by Text Match

You've got a sales table: column A has product names, column B has revenue. You want the total revenue for Apple only.

=SUMIF(A:A,"Apple",B:B)

Here, A:A is your range — the column Excel scans for matches. "Apple" is the criteria. B:B is the sum_range — the column whose values get added up. Excel goes row by row: if A2 equals Apple, it adds B2 to the running total. If A3 equals Orange, it skips B3. Simple and deterministic.

What if the product name is in a cell instead of hardcoded? Say the name you want is in cell D1. You'd write:

=SUMIF(A:A,D1,B:B)

That's a dynamic criteria reference — you can change D1 to any product name and the formula updates instantly. This is the approach you'll want in dashboards and reporting templates. No need to edit the formula every time the filter changes. Build a dropdown in D1 using data validation and suddenly your end users have a fully interactive filter without touching any formulas.

2. Sum by Number Comparison

Now let's say column A has order quantities and you want the total revenue (column B) for orders larger than 100 units.

=SUMIF(A:A,">100",B:B)

The ">100" is a text string — the operator and value together in quotes. You can use any of these comparison operators: >, <, >=, <=, =, <>. All go inside the quotes along with the number. This trips up a lot of beginners — they try to write =SUMIF(A:A,>100,B:B) without quotes and get a formula error.

If you want to make the threshold dynamic, combine the operator string with a cell reference using &:

=SUMIF(A:A,">"&E1,B:B)

Now E1 holds the threshold value. Change it from 100 to 500 and your SUMIF adjusts immediately. This pattern is especially useful in interactive reports where users can tweak parameters without touching formulas. It also makes auditing easier — the threshold is visible in a labeled cell rather than buried inside a formula string.

3. Sum by Exact Number

If you want rows where a value equals exactly a certain number, you can omit the operator entirely:

=SUMIF(A:A,100,B:B)

No quotes needed for a plain number criteria. Or use "=100" — both work. If you're referencing a cell: =SUMIF(A:A,E1,B:B) — again, no quotes since you're pointing at a cell, not hardcoding a string.

4. Cell Reference as Criteria

Beyond just numbers, you can reference a cell for any criteria type — text, dates, expressions stored as text. The key rule: if the cell contains plain text or a number, just reference it directly. No quotes around the cell reference. If the cell contains a partial expression like >500 stored as a string, Excel interprets it correctly as a comparison.

For example, if D2 contains the text >100, then =SUMIF(A:A,D2,B:B) works the same as hardcoding the string ">100". This lets you build a table of filter conditions that drive multiple SUMIF formulas at once — a powerful pattern for summary dashboards where each row has a different threshold or category.

For deeper examples of SUMIF applied to real datasets, the dedicated Excel SUMIF reference covers additional edge cases. And if you want the full syntax specification for the SUMIF function, that page breaks down every argument and optional behavior in detail.

Date Ranges, Between Two Values & NOT Equal

Three SUMIF patterns trip people up more than any others: date-based criteria, summing between two values, and the not-equal operator. Here's how each one actually works — and the common mistakes to avoid.

SUMIF for Date Ranges

Dates in Excel are stored internally as serial numbers — integers representing the number of days since January 1, 1900. That means comparison operators work on dates just like they do on regular numbers. The trick is writing the criteria string correctly.

Don't hardcode date strings like ">1/1/2024" in your criteria. Excel may interpret those as text rather than dates depending on your locale settings, giving you wrong results silently. The safe approach is the DATE() function:

=SUMIF(B:B,">="&DATE(2024,1,1),C:C)

The & concatenates the operator string with the date serial number returned by DATE(). Excel receives a numeric comparison threshold it can evaluate reliably regardless of regional date format settings. This formula sums column C wherever column B is on or after January 1, 2024.

To get a full date range — say all of Q1 2024 — combine two SUMIFs with subtraction:

=SUMIF(B:B,">="&DATE(2024,1,1),C:C)-SUMIF(B:B,">"&DATE(2024,3,31),C:C)

That subtracts everything after March 31 from everything on or after January 1 — leaving only Q1. It's a bit verbose, but it works perfectly. If you'd rather have a single formula for date ranges, switch to SUMIFS, which handles date ranges more cleanly with two separate criteria on the same column.

You can also reference date values from cells instead of DATE() — just concatenate the operator string with the cell reference: ">="&F1 where F1 contains a date. Excel will use the cell's serial number for comparison.

SUMIF Between Two Values

There's no built-in "between" operator in SUMIF — but the subtraction trick handles it cleanly. To sum column B for rows where column A is between 50 and 99 inclusive:

=SUMIF(A:A,">=50")-SUMIF(A:A,">=100")

The first SUMIF grabs everything 50 and above. The second grabs everything 100 and above. Subtracting the second from the first leaves only the 50–99 range. Clean, no array formulas needed. You can also use the SUMIFS approach if you prefer — it handles the between case with a single formula: =SUMIFS(B:B,A:A,">=50",A:A,"<100").

SUMIF NOT Equal (<>)

The not-equal operator in Excel is <>. It's used inside quotes like any other operator. To sum column B where column A is anything except Apple:

=SUMIF(A:A,"<>Apple",B:B)

To sum where column A is not empty — only rows that have something in them:

=SUMIF(A:A,"<>",B:B)

Just the operator alone, no value after it — Excel treats that as "not equal to empty string," which catches all non-blank cells. Alternatively, the pattern "<>"&"" builds the same criteria via concatenation, which some people find more explicit and readable.

The not-equal operator also works well with cell references. To sum rows where column A doesn't match whatever's in D1: =SUMIF(A:A,"<>"&D1,B:B). Change D1 and the exclusion updates dynamically — a clean way to build exclusion filters in reporting dashboards.

SUMIF vs SUMIFS: When to Upgrade

SUMIF handles one criterion. The moment you need two or more conditions at the same time — sales from Apple AND in the East region — you need SUMIFS (with an S). They're close relatives with overlapping use cases, but the syntax is different enough that knowing both matters.

Syntax Comparison

Here's SUMIF with one condition — sum column C where column A equals Apple:

=SUMIF(A:A,"Apple",C:C)

Here's the equivalent in SUMIFS — notice sum_range comes first:

=SUMIFS(C:C,A:A,"Apple")

That argument order flip is the biggest gotcha when switching between them. SUMIF is range → criteria → sum_range. SUMIFS is sum_range → range1 → criteria1 → range2 → criteria2. If you ever get a #VALUE! error right after converting a SUMIF to SUMIFS, the flipped order is almost certainly why.

Adding Multiple Conditions

Here's where SUMIFS earns its keep. To sum column C for Apple sales specifically in the East region — two conditions, both must be true:

=SUMIFS(C:C,A:A,"Apple",B:B,"East")

You can keep adding range/criteria pairs — up to 127 pairs in total. All conditions use AND logic: a row must satisfy every condition to be included in the sum. That's what you want in most cases — filter to a specific intersection of attributes.

OR Logic with SUMIFS

If you need OR logic — include rows matching this condition OR that condition — SUMIFS doesn't do it natively. The workaround is summing two SUMIFS together:

=SUMIFS(C:C,A:A,"Apple",B:B,"East")+SUMIFS(C:C,A:A,"Apple",B:B,"West")

That adds East Apple sales and West Apple sales together. For more complex OR scenarios, some people prefer SUMPRODUCT with multiplication-based logic — but that's a more advanced technique beyond the scope of this guide.

SUMIF with INDIRECT for Dynamic Ranges

One advanced pattern worth knowing: combining SUMIF with INDIRECT for dynamic sheet references. If you have a workbook where sheet names represent months and you want to pull totals from whichever month is in cell A1:

=SUMIF(INDIRECT("'"&A1&"'!A:A"),"Apple",INDIRECT("'"&A1&"'!B:B"))

This pulls the sheet name from A1, constructs the range reference as a string, and INDIRECT converts that string into a real range Excel can evaluate. It's a powerful pattern for multi-sheet consolidation dashboards. The trade-off: INDIRECT is a volatile function, meaning it recalculates on every single workbook change — not just when A1 changes. On large workbooks this can noticeably slow things down.

Which Should You Use?

Honestly, once you're comfortable with the argument order difference, you can default to SUMIFS for everything. It handles the one-condition case just as well as SUMIF, and you avoid the mental overhead of remembering which function to reach for. But SUMIF reads slightly more cleanly in simple cases, and if you're sharing workbooks with less experienced Excel users, simpler is usually better.

The full Excel SUMIFS function reference covers multi-criteria patterns in depth, including OR-logic workarounds and examples with three or more conditions. The dedicated SUMIFS function page shows how to combine SUMIFS with date ranges in a single formula — often cleaner than the two-SUMIF subtraction trick covered earlier in this guide.

Common SUMIF Errors and How to Fix Them

A few error patterns come up repeatedly when working with SUMIF — knowing them saves debugging time.

#VALUE! error: Almost always caused by range and sum_range being different sizes. If range covers 1,000 rows and sum_range covers 500, Excel throws this error. Make sure both span the same number of rows, or just use whole-column references like A:A and B:B which are always the same size.

Returns 0 when you expect a number: Three common culprits. First, your criteria column has numbers stored as text — they look like numbers but Excel treats them as strings. You can spot this if numbers in the column are left-aligned instead of right-aligned. Second, there are leading or trailing spaces in your data — "Apple " (with a trailing space) won't match "Apple". Third, you forgot quotes around text criteria and Excel interpreted it as a name reference instead of a string value.

Wrong totals: Check that you're summing the right column. It's easy to accidentally offset sum_range by one column when copy-pasting formulas. Also verify that your criteria column and sum column are actually aligned row-for-row — if you've inserted or deleted rows in one column independently, they may be out of sync.

Understanding these pitfalls puts you in a much stronger position. SUMIF is forgiving in most cases — it's the edge cases that catch people off guard.