# Excel SUMIF and SUMIFS: Syntax and Examples

The SUMIFS function in Excel adds up numbers that meet two or more conditions, while SUMIF handles a single condition. Both read a sum range, then test one or more criteria ranges against the values you supply. If you can write a SUMIF formula, you already know most of what SUMIFS needs.

## Quick Answer

- SUMIF takes three arguments: range, criteria, and sum_range. It tests one condition.
- SUMIFS takes sum_range first, then pairs of criteria_range and criteria. It tests as many conditions as you add.
- The sum range and criteria ranges in SUMIFS must be the same size, or Excel returns `#VALUE!`.
- Text criteria are not case sensitive, so `"East"` and `"east"` match the same rows.
- Wildcards work in text criteria. A question mark matches one character and an asterisk matches any run of characters.

## Syntax

SUMIF has the form:

$$=\text{SUMIF}(\text{range},\ \text{criteria},\ [\text{sum\_range}])$$

SUMIFS has the form:

$$=\text{SUMIFS}(\text{sum\_range},\ \text{criteria\_range1},\ \text{criteria1},\ [\text{criteria\_range2},\ \text{criteria2}],\ \dots)$$

| Function | Argument | Required? | Meaning |
|---|---|---|---|
| SUMIF | range | Yes | The cells tested against the criterion |
| SUMIF | criteria | Yes | The condition, such as `"East"`, `">100"`, or a cell reference |
| SUMIF | sum_range | No | The cells to add. If omitted, Excel adds the cells in range |
| SUMIFS | sum_range | Yes | The cells to add |
| SUMIFS | criteria_range1 | Yes | The first range of cells to test |
| SUMIFS | criteria1 | Yes | The condition applied to criteria_range1 |
| SUMIFS | criteria_range2, criteria2 | No | Additional range and condition pairs, up to 127 pairs |

The argument order is the detail that trips people up. SUMIF puts the sum range last and makes it optional. SUMIFS puts the sum range first and makes it required [1].

## How It Works

Excel walks through the criteria range one cell at a time. For each cell, it checks the criterion. If the test passes, Excel takes the value in the matching position of the sum range and adds it to a running total. The two ranges do not have to sit next to each other, but they must line up row for row.

SUMIFS applies the same logic to every criteria range at once. A row is added only when all conditions are true. This is an AND test, so adding a condition can only shrink the total or leave it unchanged. It can never increase it.

Criteria can be numbers, text, cell references, or expressions. A comparison like `">100"` must be written as text inside quotes. If you build the comparison from a cell, join the operator to the reference, as in `">"&F2`. Text criteria ignore case. Wildcards apply to text only, so `"W*"` matches Widget but not a number.

## Worked Example

The table below holds six sales rows with Region, Product, Units, and Revenue. Two formulas sit below the data, one using SUMIF and one using SUMIFS.

| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Region | Product | Units | Revenue |
| 2 | East | Widget | 120 | 2400 |
| 3 | East | Gadget | 80 | 1600 |
| 4 | West | Widget | 150 | 3000 |
| 5 | East | Widget | 90 | 1800 |
| 6 | West | Gadget | 60 | 1200 |
| 7 | East | Gadget | 110 | 2200 |
| 9 | Total Revenue for East (SUMIF) | `=SUMIF(A2:A7,"East",D2:D7)` | | $8,000.00 |
| 10 | Total Revenue for East Widgets (SUMIFS) | `=SUMIFS(D2:D7,A2:A7,"East",B2:B7,"Widget")` | | $4,200.00 |

The first formula adds Revenue in D2:D7 where Region in A2:A7 equals "East". Four East rows qualify: 2400, 1600, 1800, and 2200, which gives $8,000.00.

The second formula adds Revenue in D2:D7 where Region is "East" and Product is "Widget". Only rows 2 and 5 pass both tests, so 2400 plus 1800 gives $4,200.00.

Notice that the SUMIFS total is smaller. Adding the Product condition removed the two East Gadget rows from the sum.

## More Examples

**Sum above a threshold.** To total revenue over $2,000, use `=SUMIF(D2:D7,">2000")`. Because the sum range is omitted, Excel adds the cells in the criteria range itself, which is D2:D7 here.

**Sum with a cell reference.** If F2 holds the region name, `=SUMIF(A2:A7,F2,D2:D7)` reads the criterion from that cell. Change F2 and the total updates.

**Sum between two bounds.** SUMIFS handles a range of values with two conditions on the same column: `=SUMIFS(D2:D7,D2:D7,">=1500",D2:D7,"<=2500")`. Both tests apply to column D.

**Sum with a wildcard.** `=SUMIF(B2:B7,"W*",D2:D7)` matches every product starting with W and returns the Widget revenue.

**Sum excluding a value.** `=SUMIF(A2:A7,"<>East",D2:D7)` adds every row whose region is not East.

**Sum with a numeric condition.** Numeric criteria follow the same pattern. `=SUMIFS(D2:D7,A2:A7,"East",C2:C7,">100")` totals East revenue only for rows with more than 100 units.

If you need to count matching rows instead of adding them, the [COUNTIF function in Excel](/blog/data-analysis/countif-function-excel) uses the same criteria syntax. For plain addition with no conditions, see the [Excel SUM function](/blog/data-analysis/excel-sum-function-examples). When you want to multiply and add in one step, the [SUMPRODUCT function](/blog/data-analysis/excel-sumproduct-function) covers that case.

## Errors and How to Fix Them

**`#VALUE!`** appears when the sum range and a criteria range differ in size. Check that every range covers the same number of rows and columns. In the worked example, all ranges span rows 2 through 7.

**`#NAME?`** usually means a text criterion is missing its quotation marks. Write `"East"`, not `East`.

**A result of 0** often means the criterion does not match any cell. Check for trailing spaces, numbers stored as text, or a comparison written as `">100"` when the values are text.

**Wrong total from a shifted sum range.** If the sum range is a different size from the criteria range, Excel may still return a number, but it will be wrong. Keep the two ranges aligned.

**Criteria that look numeric but are text.** A cell holding `"100"` as text will not match the criterion `100`. Convert the column to numbers or use a text criterion.

## Common Mistakes

- **Reversing the argument order.** SUMIFS starts with the sum range, SUMIF ends with it. Mixing them up returns a wrong number or an error. Check the first argument of every SUMIFS formula.
- **Forgetting quotes around text criteria.** `=SUMIF(A2:A7,East,D2:D7)` fails because Excel reads East as a name. Use `"East"`.
- **Putting the operator outside the quotes.** `=SUMIF(D2:D7,">"&2000)` works, but `=SUMIF(D2:D7,">2000")` is the simpler form when the number is fixed. For a cell reference, join with `&`.
- **Using mismatched range sizes.** Every criteria range in SUMIFS must match the sum range, or SUMIFS returns `#VALUE!`. In SUMIF, a sum range of a different size is silently resized, which can shift the results.
- **Expecting case sensitivity.** `"east"` and `"East"` return the same total. If you need an exact case match, SUMIF and SUMIFS cannot do it on their own.
- **Assuming SUMIFS is available everywhere.** Older workbooks and some spreadsheet tools support SUMIF but not SUMIFS. Test before sharing a file.

## Limitations

SUMIF and SUMIFS return a single total. They cannot list the matching rows, show which records were included, or return more than one number. If you need a breakdown by region and product at the same time, a [pivot table in Excel](/blog/research-skills/pivot-table-in-excel-step-by-step-tutorial) does that in a few clicks and keeps the detail visible.

The functions are also case insensitive and cannot apply a custom test such as a regular expression. For logic that depends on several conditions joined with OR, or on a formula applied to each row, you need a different approach, such as SUMPRODUCT or a helper column. SUMIFS also recalculates over the full range each time, so on very large sheets with many criteria pairs it can slow a workbook down.

## Frequently Asked Questions

### What is the difference between SUMIF and SUMIFS?

SUMIF tests one condition and takes the sum range as its optional third argument. SUMIFS tests two or more conditions and takes the sum range as its required first argument. Use SUMIF for a single filter and SUMIFS when you need to combine filters.

### Can SUMIFS use more than two criteria?

Yes. SUMIFS accepts up to 127 criteria range and criteria pairs. Each pair adds another AND test, so every condition must be true for a row to be included in the total.

### Why does my SUMIFS formula return 0?

The most common causes are a criterion that matches no cells, numbers stored as text, or extra spaces in the criteria range. Check one condition at a time by removing pairs until the total changes, then inspect the column that stopped matching.

### Is SUMIF case sensitive?

No. Text criteria ignore case, so `"east"`, `"East"`, and `"EAST"` all match the same rows. To force a case sensitive match you need a different formula, such as one built on SUMPRODUCT with the EXACT function.

### Can I use wildcards in SUMIFS criteria?

Yes, for text criteria. A question mark matches exactly one character and an asterisk matches any sequence of characters. To find a literal question mark or asterisk, put a tilde in front of it, as in `"~*"`.

## References

1. [Altman DG, Bland JM (2011). Brackets (parentheses) in formulas. BMJ](https://doi.org/10.1136/bmj.d570)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics &amp; Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)
- [Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1008984)

## Related Articles

- [COUNTIF Function in Excel: Syntax and Examples](/blog/data-analysis/countif-function-excel)
- [Excel SUM Function: Syntax, Examples and Tips](/blog/data-analysis/excel-sum-function-examples)
- [Excel SUMPRODUCT Function: Syntax and Examples](/blog/data-analysis/excel-sumproduct-function)
- [DATEDIF Excel Function: Syntax and Examples](/blog/data-analysis/datedif-excel-function)
- [IF AND Statements in Excel: Syntax and Examples](/blog/data-analysis/if-and-statements-excel)
- [Residual Sum of Squares: Formula and Example](/blog/research-skills/residual-sum-of-squares-formula-and-example)
- [Statistical Symbols and Notation: A Quick Reference](/blog/guides/statistical-symbols-and-notation-a-quick-reference)
- [Tabular Data: What It Is and How to Analyze It](/blog/research-skills/tabular-data-what-it-is-and-how-to-analyze-it)