SUMIF checks one range for a condition and adds numbers from matching rows. It can total sales for one city or expenses for one product category.
SUMIF syntax
The syntax is SUMIF(range, criteria, [sum_range]). Range is checked against the condition, while sum_range contains the numbers to add.
The third argument can be omitted when the same cells are both tested and added. When using separate columns, the criteria range and sum range should have matching dimensions.
Total sales by city
If B2:B100 contains cities and C2:C100 contains sales, the formula below totals rows for London.
Before using it, confirm that amounts are numeric and city names do not contain unwanted spaces.
How to do it
- Select the range containing the condition.
- Enter the text or numeric criterion.
- Select the aligned numeric sum range and validate the result with a filter.
=SUMIF(B2:B100,"London",C2:C100)What changes when a return is included?
A is city, B status, and C amount. The Istanbul rows contain 1,200, 400, and 500. SUMIF on city returns 2,100, including the returned order of 400. To measure only completed orders, you need a second status criterion.
SUMIFS puts the sum range first, then each criteria range and criterion. The formula below adds only Istanbul and Completed rows and returns 1,700. Decide separately how returns should be signed and defined in a real report.
| Row | City | Status | Amount |
|---|---|---|---|
| 2 | Istanbul | Completed | 1,200 |
| 3 | Ankara | Completed | 800 |
| 4 | Istanbul | Returned | 400 |
| 5 | Istanbul | Completed | 500 |
=SUMIFS(C2:C5,A2:A5,"Istanbul",B2:B5,"Completed")Check aligned ranges and number types
C2:C5, A2:A5, and B2:B5 must span the same rows. A different spelling of the city can omit a record. Also check whether 1,200 is a number or text in Excel; text amounts may not be summed as expected.
This page now includes the former SUMIFS guide's multi-criteria example. SheetCommand can display an overall numeric-column sum but does not insert this two-condition Excel formula.
=SUMIF(A2:A5,"Istanbul",C2:C5)Why conditional totals can be wrong
Criteria and sum ranges that start on different rows or have different sizes can misalign records. Start and end both ranges on the same rows.
Use SUMIFS for multiple conditions. Amounts stored as text are not added, so check the numeric column's data type.
Frequently asked questions
Can SUMIF handle multiple conditions?
SUMIF is designed for one condition. Use SUMIFS when several conditions must be met.
Does SheetCommand write SUMIF into the file?
No. This guide teaches the formula; the tool can show basic totals by command and help clean the source data.
When should I use SUMIF or SUMIFS?
Use SUMIF for one condition and SUMIFS when city and status must both match. The example results are 2,100 and 1,700.
Sources and further reading
SheetCommand
Prepare data before a conditional total
Summarize the report and remove duplicate or blank rows before applying SUMIF in Excel.