# How to Use SUMIF in Excel (Step-by-Step with Examples)

**By StackTrekker Editorial** · Published August 24, 2026 · Updated September 26, 2026
SUMIF is the formula that answers the most common spreadsheet question there is, *"how much did X total?"*: how much did we sell in January, how much did this client spend, how many hours went to that project.

If you can only learn one conditional formula in Excel, learn SUMIF. It's simpler than SUMIFS, covers most real-world cases, and takes five minutes to understand.

## What SUMIF does

SUMIF adds up the cells in one range, but only when a matching condition is true in another range.

```
=SUMIF(range, criteria, [sum_range])
```

| Part | What it means |
|---|---|
| `range` | The cells you check the condition against |
| `criteria` | The condition to match |
| `sum_range` | The cells to add up (optional; if omitted, Excel sums `range` itself) |

This matches the official argument list in [Microsoft's SUMIF documentation](https://support.microsoft.com/en-us/excel/functions/sumif-function), which is the version to check whenever a formula behaves differently from the examples here.

The classic example: a sales table with `Category` in column A and `Amount` in column B. You want the total for "Coffee" only.

```
=SUMIF(A2:A100, "Coffee", B2:B100)
```

Excel looks at each cell in A2:A100; wherever it finds "Coffee," it adds the matching cell from B2:B100. Everything else is ignored.

## Real examples

### Example 1: Sum by exact text

**Sales table:** column A = region, column B = sales.

```
=SUMIF(A2:A50, "West", B2:B50)
```

Returns the total sales for the West region only.

### Example 2: Sum with a cell reference

Typing the criteria into the formula works, but referencing a cell is better; change the cell and the formula updates:

```
=SUMIF(A2:A50, D1, B2:B50)
```

Where D1 contains "West". This is how you build a small dashboard: one criteria cell, a few formulas pointing at it.

### Example 3: Sum values greater than a number

Criteria can be operators:

```
=SUMIF(B2:B50, ">100", B2:B50)
```

Adds up every sale over 100. Note that when the criteria is a comparison, `sum_range` is usually the same as `range`.

### Example 4: Wildcards for partial matches

Use `*` for "any text" and `?` for "any single character":

```
=SUMIF(A2:A50, "Coff*", B2:B50)
```

Matches "Coffee", "Coffee beans", "Coffee cups", and anything else starting with "Coff".

## Criteria operators you can use

| Operator | Meaning | Example criteria |
|---|---|---|
| `"text"` | Exact match | `"West"` |
| `"="` | Equal to | `"=West"` |
| `"<>"` | Not equal to | `"<>West"` |
| `">"` `"<"` | Greater or less than | `">100"` |
| `">="` `"<="` | Greater or equal, less or equal | `"<=500"` |
| `"<>"` (alone) | Not blank | `"<>"` |
| `"*"` `"?"` | Wildcards | `"Coff*"`, `"C?ffee"` |

Two useful patterns that follow from this table:

- **Sum everything that is not blank:** `=SUMIF(A2:A50, "<>", B2:B50)` ignores empty criteria cells and adds only rows with a value.
- **Combine operators with a cell reference:** `=SUMIF(B2:B50, ">"&D1, B2:B50)` sums everything above whatever number sits in D1.

## Hidden rows, filtered rows, and full-column references

Three behaviours that surprise people, and each one has a fix:

**1. SUMIF includes hidden and filtered-out rows.** Unlike `SUBTOTAL`, SUMIF does not care whether a row is visible. If you filter a table to "West" and sum column B with SUMIF, you still get the whole column. To sum only visible rows, use `=SUBTOTAL(109, B2:B50)`, where 109 means "sum, ignore hidden rows". Excel also has `AGGREGATE` for the same job with more options.

**2. Full-column references work, but cost time.** `=SUMIF(A:A, "West", B:B)` is valid and handy while you are still adding rows, but on large workbooks it makes Excel scan far more cells than it needs to. Aim the ranges at your data, or use a named range, once the sheet settles.

**3. SUMIF does not do OR conditions.** `=SUMIF(A2:A50, "West", B2:B50)` plus `=SUMIF(A2:A50, "East", B2:B50)` added together is the usual workaround, or switch to an array-style formula if you are comfortable with those.

## SUMIF vs SUMIFS: which to use?

SUMIFS is the newer, more flexible version; it allows **multiple conditions**:

```
=SUMIFS(sum_range, range1, criteria1, range2, criteria2)
```

Notice the order is different: the sum range comes **first** in SUMIFS, which is also how [Google Sheets documents SUMIFS](https://support.google.com/docs/answer/3093281) and how Microsoft lists it in the Excel function reference.

**Rule of thumb:** with one condition, SUMIF is fine. With two or more conditions, or when the sum range is a different shape than the criteria range, use SUMIFS. There's no penalty for using SUMIFS for everything once you're comfortable with it.

## Common mistakes (and how to avoid them)

**1. Wrong range sizes.** `range` and `sum_range` must be the same size. `=SUMIF(A2:A100, "Coffee", B2:B50)` silently gives wrong results because the ranges are different lengths. Always match the rows.

**2. Criteria text needs quotes.** Text and operators go in quotes: `"Coffee"`, `">100"`. Numbers and cell references don't: `100`, `D1`.

**3. Case doesn't matter, but spelling does.** SUMIF is case-insensitive ("COFFEE" matches "coffee"), but a typo matches nothing. If your result is 0, check the spelling first.

**4. Leading zeros and numbers stored as text.** If your data was imported, values might be text disguised as numbers. The fix: select the column, then go to Data > Text to Columns and click Finish. That converts text-numbers to real numbers SUMIF can add.

## How to use SUMIF in Google Sheets

Everything above works identically in Google Sheets; the syntax is the same, and Google documents the argument list in [the SUMIF page in the Docs editors help](https://support.google.com/docs/answer/3093583). The only difference worth knowing: Google Sheets has a few extra functions like SUMIFS with different edge-case behaviors, but for everyday use, copy-paste between Excel and Sheets works fine.

If you are building dropdown-driven reports next to these formulas, our [data validation dropdown guide](/data-validation-dropdown/) covers the list setup, and the dropdown criteria can then feed a SUMIF directly.

## SUMIF with multiple conditions? Use SUMIFS

You'll quickly hit a case where one condition isn't enough: *"total sales for the West region in January."* That's two conditions: region and month. SUMIF can't do it; SUMIFS can:

```
=SUMIFS(sum_range, range1, criteria1, range2, criteria2)
```

```
=SUMIFS(C2:C100, A2:A100, "West", B2:B100, "January")
```

Three things to remember with SUMIFS:

1. **The sum range comes first**, the opposite of SUMIF and the #1 source of broken SUMIFS formulas.
2. Every criteria range should be the same size as the sum range.
3. SUMIFS also supports wildcards and operators exactly like SUMIF: `=SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">100")`.

**When to use which:** SUMIF for one condition, SUMIFS for two or more. There's no downside to standardizing on SUMIFS once you're comfortable with the argument order.

## Combining SUMIF with IF for totals that change

A useful pattern: use IF to categorize on the fly, then SUM over the result. Say you have a list of transactions and want to flag anything above 500 as "Large":

```
=SUM(IF(B2:B100 > 500, 1, 0))
```

Entered as an array formula (Ctrl+Shift+Enter in older Excel; Enter in Microsoft 365 and Google Sheets), this counts the number of transactions over 500. It's a different tool than SUMIF (SUMIF adds values; this counts rows), but the two together cover most "conditional total" problems.

## Debugging a SUMIF that returns wrong numbers

When SUMIF gives a result that's obviously wrong (or zero), work through this checklist in order:

1. **Range sizes:** `range` and `sum_range` must span the same rows. A mismatch silently returns a wrong total, no error message.
2. **Criteria spelling:** SUMIF is case-insensitive but not typo-tolerant. Copy the exact value from a data cell rather than retyping it.
3. **Text vs numbers:** imported data often stores numbers as text. SUMIF won't match `123` against `"123"` stored as text. Fix: select the column, then go to Data > Text to Columns and click Finish.
4. **Hidden characters:** copied values sometimes carry invisible spaces or line breaks. Use `=TRIM(A2)` in a helper column, or check with `=LEN(A2)` against a known-good cell.
5. **Wrong column order:** check that `range` and `sum_range` really point at the right columns. It's embarrassingly common to sum the wrong column.

## SUMIF vs pivot tables

If you find yourself writing five SUMIFs for the same table (total by region, by month, by product), a pivot table does all of that in one shot with a drag-and-drop interface, no formulas to maintain. SUMIF's sweet spot is a **single conditional total** that needs to update live as data changes, or a total embedded inside a larger formula (like a dashboard cell that references other cells). Pivot tables are better for exploratory analysis and multi-dimension summaries; SUMIF is better for formula-driven reports.

## Google Sheets: same formula, same syntax

SUMIF works identically in Google Sheets, so copy-paste between the two tools works for this function. Two Google-specific notes:

- Google Sheets has a bonus: `=SUMIF(range, criteria, sum_range)` supports **array criteria** in newer versions, letting you sum across multiple match values at once.
- The QUERY function (`=QUERY(A1:C, "select A, sum(C) where B = 'January' group by A", 1)`) is a heavier but far more powerful alternative if you're doing data analysis rather than report building.

## Practice file

The fastest way to learn is to build it. Make a small table: 20 rows of `Item | Category | Amount`, then write:

1. Total for one category (exact match)
2. Total over a threshold (comparison)
3. Total with a wildcard
4. Total with two conditions (switch to SUMIFS)

If all four return sensible numbers, you've got SUMIF covered for real-world use.

Two related skills round out the spreadsheet toolkit: when a lookup returns `#N/A`, the fixes in our [VLOOKUP troubleshooting guide](/sheets-vlookup-error/) apply to SUMIF's sibling functions too, and if you're still deciding between Excel and Google Sheets for this kind of work, our [Excel vs Google Sheets comparison](/excel-vs-sheets/) covers the practical differences.

The text-to-number conversion in mistake 4 has its own walkthrough: see the [split text to columns fix](/split-text-to-columns-fix/) for the step-by-step version.

## FAQ

**Can SUMIF look at two columns at once?**
Not directly; that's what SUMIFS is for. Use SUMIFS when you need multiple conditions.

**Why is my SUMIF returning 0?**
Most common causes: criteria misspelled, range sizes mismatched, or numbers stored as text. Work through those three in order.

**Does SUMIF work on other sheets in the workbook?**
Yes, reference them normally: `=SUMIF(Sheet2!A2:A50, "Coffee", Sheet2!B2:B50)`.

**Does SUMIF ignore hidden rows?**
No. It sums hidden and filtered-out rows along with the visible ones. Use `SUBTOTAL(109, range)` when you need visible rows only.

## Sources

- [Microsoft: SUMIF function](https://support.microsoft.com/en-us/excel/functions/sumif-function)
- [Google Sheets: SUMIF](https://support.google.com/docs/answer/3093583)
- [Google Sheets: SUMIFS](https://support.google.com/docs/answer/3093281)

*Last updated: August 24, 2026.*

---

Source: https://stacktrekker.com/excel-sumif-formula/