The Excel SUMIF function adds the numbers in a range that meet a single condition — for example, every order over $100, or every row tagged “apples.”
Syntax
| Argument | Description | |
|---|---|---|
range | Required | The cells tested against the criteria. |
criteria | Required | The condition, e.g. ">10", "apples", or a cell reference. Supports the wildcards * and ?. |
sum_range | Optional | The cells to add. If omitted, SUMIF adds the cells in range itself. |
How to use it
SUMIF tests one range and adds matching numbers. Leave off sum_range to add the tested cells themselves, or supply it to add a different column.
Wrap operators and text in double quotes. To compare against a cell, concatenate with &, e.g. ">"&E1. For two or more conditions, step up to SUMIFS.
Same shape: range and sum_range should be the same size and orientation. If sum_range is shorter, Excel extends it to match the test range, which can silently include the wrong cells.
Try it: interactive demo
Pick a SUMIF example to see the formula and its result.
Practice workbook
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
Can SUMIF use wildcards?
* matches any number of characters and ? matches a single character — so "app*" matches apple and apples. To match a literal * or ?, precede it with a tilde (~*).How do I compare against a cell value in SUMIF?
& operator: =SUMIF(A2:A6,">"&E1) sums values greater than whatever is in E1.Why does SUMIF return 0?
sum_range points at non-numeric cells. Confirm the test column and the sum column line up row-for-row.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class