When a spreadsheet contains hundreds or thousands of rows, adding everything manually is rarely useful. SUMIF and SUMIFS let you total only the rows that meet conditions you specify.
Use SUMIF when one condition is enough. Use SUMIFS when several conditions must be true at the same time.
SUMIF vs SUMIFS
| Need | Function | Example question |
|---|---|---|
| One condition | SUMIF | How much did North region sell? |
| Multiple conditions | SUMIFS | How much did North sell in January? |
SUMIF syntax
The first range is where Excel checks the condition. The criteria says what to look for. The optional sum range contains the numbers to add.
Example: total sales for one region
| Region | Sales |
|---|---|
| North | $1,200 |
| South | $900 |
| North | $1,450 |
| East | $700 |
If regions are in A2:A5 and sales are in B2:B5, use:
The result is $2,650.
Use a cell as the criterion
Hard-coding "North" works for a quick calculation, but a cell reference is easier when the user needs to change the region.
If D2 contains North, the result is the same. Change D2 to South and the formula recalculates without editing the formula itself.
SUMIFS for multiple conditions
Notice that SUMIFS starts with the range to be added. This is an easy detail to miss when moving between SUMIF and SUMIFS.
Example: sales by region and month
| Date | Region | Sales |
|---|---|---|
| Jan 5 | North | $1,200 |
| Jan 8 | South | $900 |
| Jan 14 | North | $1,450 |
| Feb 3 | North | $800 |
Suppose dates are A2:A5, regions are B2:B5 and sales are C2:C5. To total North sales in January, you can use date boundaries:
The second date condition is deliberately less than the first day of the following month. That approach avoids guessing how many days are in the month and also works when the source cells contain times.
Use comparison criteria
SUMIF and SUMIFS can use criteria such as greater than, less than, equal to and not equal to.
Because the comparison operator is part of the criteria, it is written inside quotation marks. When the comparison value is stored in a cell, join the operator to the cell reference:
Common SUMIF and SUMIFS mistakes
- Wrong argument order: SUMIFS starts with sum_range; SUMIF does not.
- Ranges do not line up: The criteria and sum ranges should represent corresponding rows.
- Text criteria are not quoted: Use quotation marks for literal text and comparison expressions.
- Dates are treated as text: Make sure the source cells contain real Excel dates when filtering by date.
- Month boundaries are incomplete: A condition such as >= January 1 should normally be paired with < February 1 for a complete January range.
- Hidden spaces: A value that looks like North may contain an extra space and fail to match the expected criterion.
When SUMIF is enough
Use SUMIF when there is one rule. For example, if the only question is total sales for North, SUMIF is simpler than building a SUMIFS formula with unnecessary criteria.
When SUMIFS is the better choice
Use SUMIFS when the question naturally sounds like total X where condition A and condition B are both true. Typical examples include sales for one salesperson in one region, expenses for one category in a date range, or invoices above a threshold that are still unpaid.
SUMIF and SUMIFS with dates
Dates are one of the most useful applications because a reporting period can be expressed as a pair of boundaries. For a month, use the first day of the month as the lower bound and the first day of the next month as the exclusive upper bound. This is generally safer than using a fixed number of days.
Practical checklist
- Identify the rows that should qualify before writing the formula.
- Confirm the column containing the condition and the column containing the values to add.
- Use SUMIF for one condition and SUMIFS for multiple conditions.
- Check that dates are stored as dates, not text.
- Test the result against a small sample you can verify manually.
- Use cell references for criteria that users will change regularly.
Related Excel guides
If you need to count matching rows rather than add their values, see Tervilo's COUNTIF and COUNTIFS guide. For decision rules, see How to Use IF in Excel. Browse the full Excel Guides collection for more spreadsheet workflows.
Quick answer
Use SUMIF for one condition and SUMIFS for multiple conditions. Build the criteria around the actual question you are trying to answer, then verify the result with a small sample before relying on it for a report.