Math
SUM function in Excel:syntax and examples
SUM adds the numbers in one or more ranges and cells. In Spanish Excel it is called SUMA. Syntax: =SUM(number1, [number2], ...).
Syntax
=SUM(number1, [number2], ...)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 |
|---|---|
number1 | Required. A range, a cell or a number. Example: B2:B6. |
number2, ... | Optional. More ranges, cells or numbers, up to 255 in total. Example: C2:C6. |
How do you use SUM? A step-by-step example
SUM gives you the total of one column, several columns or loose cells. In this example you have the expenses of two months and you want the total for November.
| A | B | C | |
|---|---|---|---|
| 1 | Item | November | October |
| 2 | Rent | 650.00 | 120.00 |
| 3 | Electricity | 58.40 | 45.00 |
| 4 | Internet | 35.00 | 35.00 |
| 5 | Groceries | 312.75 | 290.00 |
| 6 | Transport | 40.00 | 40.00 |
In B7 you type =SUM(B2:B6) and it returns 1096.15.
- Type
=SUM(in the total cell. - Select B2 to B6 with the mouse. Excel writes
B2:B6, which means "from B2 to B6". - Close the parenthesis and press Enter.
Some countries (Spain and much of Europe and Latin America) use semicolons between arguments in Excel: =SUM(B2;B6).
With the keyboard: go to B7 and press <kbd>Alt</kbd> + <kbd>=</kbd> (on Mac, <kbd>Cmd</kbd> + <kbd>Shift</kbd> + <kbd>T</kbd>). Excel suggests the range above.
How do you add several ranges or loose cells?
Separate each part with a comma. SUM takes up to 255.
| You want to add | Formula | Result |
|---|---|---|
| Both months | =SUM(B2:B6,C2:C6) | 1626.15 |
| The same block, as one range | =SUM(B2:C6) | 1626.15 |
| Rent, internet and transport | =SUM(B2,B4,B6) | 725 |
| November plus 100 for surprises | =SUM(B2:B6,100) | 1196.15 |
| November without the rent | =SUM(B2:B6)-B2 | 446.15 |
SUM or the plus sign?
For a range, SUM. Writing =B2+B3+B4+B5+B6 gives the same 1096.15, but it breaks as soon as the range grows and it treats text differently:
| The cell holds | With SUM | With the plus sign |
|---|---|---|
| A number | Adds it | Adds it |
| A number stored as text ("35") | Ignores it | Converts it and adds it |
| Text ("pending") | Ignores it | #VALUE! |
| Nothing | Ignores it | Counts 0 |
To add with a condition (only groceries, only one region), do not use SUM with filters: use SUMIF.
How does SUM show up in an Excel test?
In a job interview SUM almost never comes alone. It is usually inside another question: "What share of the total is each expense?" The answer is =B2/SUM($B$2:$B$6), with the total locked with $ so you can drag it.
In a skills test:
What does
=SUM(B2,B4,B6)return? a) The sum of B2 to B6 · b) The sum of B2, B4 and B6 · c) #VALUE! · d) The average of the three cells
Answer: b. The comma separates loose cells; the colon marks a range. To get the average instead, see AVERAGE. For the highest and lowest values, see MAX and MIN.
Practice SUM
The English exercises are on the way. Until then, copy the expenses table into a blank sheet, add each month, then type 35 as text in B4 and watch the total drop.
Available in: all versions of Excel. Source: Microsoft: SUM function. Results calculated with Formulo's engine.
Common mistakes
- The total is lower than it should be
- A number is stored as text: it usually shows aligned to the left with a green triangle. SUM does not count it. With 35 as text in B4, =SUM(B2:B6) gives 1061.15 instead of 1096.15. Select the cell, click the warning and choose Convert to Number.
- The total includes rows you filtered out
- SUM adds every row in the range, including the ones a filter hides. To add only the rows the filter leaves visible, use =SUBTOTAL(9,B2:B6).
- #VALUE! when adding with +
- With +, Excel tries to turn each cell into a number and gives #VALUE! if it finds text such as "pending". SUM ignores that text. Use SUM for ranges.
- The formula shows as text and does not calculate
- The cell is formatted as Text or the = at the start is missing. Change the format to General, type the formula again and press Enter.
Frequently asked questions
What is SUM called in Spanish?
SUMA. In a Spanish Excel it is written =SUMA(B2:B6).
What is the AutoSum shortcut?
Alt + = on Windows and Cmd + Shift + T on Mac. Excel writes the SUM function with the range above or to the left. Check the range before you press Enter.
What is the difference between =SUM(B2:B6) and =B2+B3+B4+B5+B6?
With numbers they give the same. With a number stored as text, + converts it and adds it, and SUM does not. With text such as "pending", + gives #VALUE! and SUM ignores it.
How do I add only the cells that meet a condition?
With SUMIF for one condition and SUMIFS for several.
How do I add cells that are not next to each other?
Separate them with commas: =SUM(B2,B4,B6). You can also hold Ctrl while you select them.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.