Math
SUMIF function in Excel:syntax and examples
SUMIF adds the values in a range that meet a condition. In Spanish Excel it is called SUMAR.SI. Syntax: =SUMIF(range, criteria, [sum_range]).
Syntax
=SUMIF(range, criteria, [sum_range])In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
range | Required. The cells where the condition is checked. Example: B2:B9, the column of cities. |
criteria | Required. The condition, in quotes if it is text or has an operator. Examples: "Austin", ">100", F2. |
sum_range | Optional. The cells that are added. If you leave it out, it adds the range itself, which only makes sense with number criteria such as ">100". Example: C2:C9. |
How do you use SUMIF? A step-by-step example
SUMIF adds up a column, but only the rows that meet a condition. In this example you have 8 orders from an online store and you want the total sold in Austin.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | City | Amount | Status |
| 2 | N-201 | Austin | 85.00 | Paid |
| 3 | N-202 | Denver | 240.00 | Pending |
| 4 | N-203 | Austin | 132.50 | Paid |
| 5 | N-204 | Boston | 60.00 | Pending |
| 6 | N-205 | Denver | 410.00 | Paid |
| 7 | N-206 | Austin | 99.90 | Pending |
| 8 | N-207 | New York | 175.00 | Paid |
| 9 | N-208 | Denver␣ | 55.00 | Paid |
Cell B9 has a space at the end (marked with ␣). You cannot see it in Excel.
=SUMIF(B2:B9,"Austin",C2:C9) returns 317.4: 85 + 132.5 + 99.9.
B2:B9is where the condition is checked: the cities."Austin"is the condition.C2:C9is what gets added: the amounts in the rows that match.
What can you put in the criteria?
| You want to add | Formula | Result |
|---|---|---|
| The orders from one city | =SUMIF(B2:B9,"Austin",C2:C9) | 317.4 |
| The city that F2 says | =SUMIF(B2:B9,F2,C2:C9), with Austin in F2 | 317.4 |
| The orders still pending | =SUMIF(D2:D9,"Pending",C2:C9) | 399.9 |
| Amounts over 100 | =SUMIF(C2:C9,">100") | 957.5 |
| Amounts greater than F1 | =SUMIF(C2:C9,">"&F1), with 100 in F1 | 957.5 |
| Cities that start with Denver | =SUMIF(B2:B9,"Denver*",C2:C9) | 705 |
When the condition is on the same column you are adding (amounts over 100), you do not need the third argument.
SUMIF or SUMIFS?
SUMIF takes one condition. For several, use SUMIFS, available since Excel 2007. The change that causes the most errors: the range you add moves to the front.
| SUMIF | SUMIFS | |
|---|---|---|
| Conditions | 1 | Up to 127 |
| Where the sum range goes | At the end, and it is optional | At the start, and it is required |
| Example | =SUMIF(B2:B9,"Austin",C2:C9) | =SUMIFS(C2:C9,B2:B9,"Austin",D2:D9,"Pending") |
| Result with this data | 317.4 | 99.9 |
To count rows instead of adding amounts, use COUNTIF. To mark each row instead of adding, use IF. For a value from a table, use VLOOKUP.
How does SUMIF show up in an Excel test?
In a job interview it is the classic summary by category: you have the month's expenses and a list of categories in F2:F5, and they ask for the total of each. The answer is =SUMIF($B$2:$B$40,F2,$C$2:$C$40), dragged down, with the ranges locked and the criteria pointing to the cell.
In a skills test:
What does
=SUMIF(B2:B9,"Austin")return if B2:B9 only holds city names? a) The sum of the Austin amounts · b) 3 · c) 0 · d) #VALUE!
Answer: c. Without sum_range, it adds the cells of B2:B9 that meet the criteria, and those are text.
Practice SUMIF
The English exercises are on the way. Until then, copy the orders table into a blank sheet and try each formula in the criteria table, then change the city in F2 and watch the total move.
Available in: all versions of Excel. SUMIFS, since Excel 2007. Source: Microsoft: SUMIF function. Results calculated with Formulo's engine.
Common mistakes
- It returns 0
- sum_range is missing and the criteria is text. =SUMIF(B2:B9,"Austin") tries to add the cities, which are text, and returns 0. Add the column of amounts: =SUMIF(B2:B9,"Austin",C2:C9) returns 317.4.
- It adds less than you can see
- A cell has an extra space ("Denver "). With the data on this page, =SUMIF(B2:B9,"Denver",C2:C9) returns 650 instead of 705. Clean the column with TRIM or use the wildcard: "Denver*" returns 705.
- The ranges have different sizes
- If sum_range is shorter than range, Excel does not warn you: it stretches it to the same size. =SUMIF(B2:B9,"Austin",C2:C5) returns 317.4, the same as with C2:C9. It works, but the day someone changes the sheet, it breaks. Always write ranges of the same size.
- A criteria with a cell returns 0
- You wrote the cell inside the quotes: ">F1" searches for the text F1. Write it as ">"&F1. On the amounts, =SUMIF(C2:C9,">F1") returns 0 and =SUMIF(C2:C9,">"&F1) returns 957.5.
Frequently asked questions
What is SUMIF called in Spanish?
SUMAR.SI. In a Spanish Excel: =SUMAR.SI(B2:B9;"Austin";C2:C9).
How do I add with two or more conditions?
With SUMIFS. Watch out: the range you add goes first: =SUMIFS(C2:C9,B2:B9,"Austin",D2:D9,"Pending") returns 99.9.
Is SUMIF case-sensitive?
No. "austin" and "Austin" add the same: =SUMIF(B2:B9,"austin",C2:C9) returns 317.4.
How do I get an average with a condition?
Divide SUMIF by COUNTIF: =SUMIF(B2:B9,"Austin",C2:C9)/COUNTIF(B2:B9,"Austin") returns 105.8. Or use AVERAGEIF, which does it in one step.
Can I use SUMIF with dates?
Yes, with the operator in quotes and the date in a cell: =SUMIF(A2:A50,">="&F1,C2:C50) adds from the date in F1.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.