Excel SUM Function
last modified October 5, 2026
The SUM function is one of the most fundamental and frequently used
functions in Excel. It adds all numbers in a range of cells or individual
values. This tutorial provides a comprehensive guide to using the
SUM function with detailed examples. You'll learn basic syntax,
practical applications, and advanced techniques to master this essential Excel
function.
SUM Function Basics
The SUM function adds values together. It can handle individual
numbers, cell references, ranges, or a mix of all three. The syntax is simple
and flexible.
| Component | Description |
|---|---|
| Function Name | SUM |
| Syntax | =SUM(number1, [number2], ...) |
| Arguments | 1-255 items to sum |
| Return Value | Sum of all arguments |
This table breaks down the essential components of the SUM
function. It shows the function name, basic syntax format, argument limits, and
return value characteristics.
| Argument | Meaning |
|---|---|
| number1 | Required. The first number, cell reference, or range to add. |
| number2, ... | Optional. Additional numbers, cell references, or ranges, up to a total of 255 arguments. |
You can mix literals, cell references, and ranges in the same call. The
function behaves differently depending on where a value comes from. Inside a
range, SUM counts only numbers: empty cells, text, and logical
values such as TRUE and FALSE are ignored, while
cells that hold zero are included. Values typed directly into the argument list
are more aggressive: logical values and text that looks like a number are
converted and counted. The following examples explore each of these cases.
Basic SUM Example
This example demonstrates the simplest use of the SUM function with individual numbers.
=SUM(5, 10, 15)
This formula adds three numbers directly: 5, 10, and 15. The result will be 30. This shows how SUM can work with hard-coded values without cell references.
SUM with Cell References
A more practical use of SUM involves adding values from specific
cells. Here's an example with cell references.
| A | B |
|---|---|
| 10 | |
| 20 | |
| 30 | |
| =SUM(A1:A3) |
The table shows a simple spreadsheet with values in column A and a
SUM formula in cell B4 that adds up the values from A1 to A3.
=SUM(A1:A3)
This formula sums values in cells A1 through A3. The result will be 60 (10+20+30). Using ranges makes SUM more powerful for working with data in spreadsheets.
SUM with Multiple Ranges
SUM can handle multiple ranges or combinations of ranges and individual cells. This example shows this flexibility.
| A | B | C |
|---|---|---|
| 5 | 10 | |
| 15 | 20 | |
| =SUM(A1:B2, 25) |
This table demonstrates SUM's ability to combine different types of arguments. It shows values in cells A1 through B2 plus an additional number 25 being summed in cell C3.
=SUM(A1:B2, 25)
This formula sums all values in range A1:B2 (5+10+15+20) plus an additional 25. The result will be 75. This demonstrates SUM's ability to combine different types of arguments.
SUM with Non-Adjacent Cells
You can sum non-adjacent cells by listing them individually or using the Ctrl key to select multiple cells while creating the formula.
| A | B | C |
|---|---|---|
| 10 | 20 | |
| 30 | 40 | |
| =SUM(A1, B2, A3) |
The table illustrates how to sum specific, non-adjacent cells (A1, B2, and A3) while ignoring other values in the range. Cell A3 is empty in this example.
=SUM(A1, B2, A3)
This formula sums only specific cells: A1 (10), B2 (40), and A3 (empty, ignored). The result will be 50. This technique is useful when you need to sum scattered cells meeting certain criteria.
SUM with Text and Numbers
SUM automatically ignores text values in referenced cells. This example shows how SUM handles mixed content.
| A | B |
|---|---|
| 10 | |
| Text | |
| 20 | |
| =SUM(A1:A3) |
This table shows how SUM behaves when a range contains both numbers
and text. The text value in A2 is ignored in the calculation, demonstrating
SUM's handling of mixed data types.
=SUM(A1:A3)
The formula sums A1 (10) and A3 (20), ignoring A2 ("Text"). The result is 30.
This behavior makes SUM robust when working with datasets
containing mixed content.
Note the opposite case: numbers stored as text, such as "20" entered with a
leading apostrophe or pasted from another program, are ignored just like any
other text. Such cells usually carry a green error indicator. Convert them with
VALUE or NUMBERVALUE before summing, for example
=SUM(VALUE(A1:A3)).
SUM with Blank Cells
SUM ignores blank cells, which is equivalent to adding zero to the total. Cells that hold the number zero, on the other hand, are counted normally. This example demonstrates this behavior.
| A | B |
|---|---|
| 15 | |
| 25 | |
| =SUM(A1:A3) |
The table contains numbers and blank cells to demonstrate how SUM
handles empty cells. The blank cell A2 is ignored in the calculation.
=SUM(A1:A3)
The formula sums A1 (15) and A3 (25), ignoring blank A2. The result is 40. This automatic handling of blanks makes SUM convenient for incomplete datasets.
SUM Across Worksheets
SUM can reference cells across multiple worksheets. This example shows how to sum the same cell from different sheets.
=SUM(Sheet1:Sheet3!A1)
This formula sums cell A1 from Sheet1, Sheet2, and Sheet3. If A1 contains 10, 20, and 30 respectively, the result is 60. This 3D reference capability is powerful for consolidating data.
SUM with Logical Values
Logical values behave differently depending on where they come from. Inside a
referenced range they are ignored. Typed directly into the argument list,
TRUE counts as 1 and FALSE as 0. This is a common
source of confusion, so both cases are shown here.
| A | B |
|---|---|
| 10 | |
| TRUE | |
| FALSE | |
| =SUM(A1:A3) | |
| =SUM(10, TRUE, FALSE) |
The table mixes a number with logical values. The first formula reads the values from the range, where logicals are ignored; the second passes them as direct arguments, where they are converted.
=SUM(A1:A3) ' 10, logicals in the range are ignored =SUM(10, TRUE, FALSE) ' 11, direct TRUE counts as 1, FALSE as 0
The range formula sees only the numeric cell A1 (10), so it returns 10. The second formula counts the logical values because they are typed directly: TRUE becomes 1 and FALSE 0, giving 11. Text follows the same pattern, which the next table summarizes.
Direct Arguments versus Range References
The same value can give different results depending on how it reaches
SUM. The table summarizes the rules.
| Input | In a range or array | Typed directly |
|---|---|---|
| Number, such as 10 | 10 | 10 |
| Numeric text, such as "5" | Ignored | Counted as 5 |
| Text, such as "abc" | Ignored | #VALUE! error |
| TRUE / FALSE | Ignored | Counted as 1 / 0 |
| Empty cell | Ignored | Counts as 0 |
| Zero | Counted as 0 | Counted as 0 |
=SUM(1, "2", TRUE) ' 4, direct numeric text and logicals are converted =SUM(1, "x", TRUE) ' #VALUE!, direct non-numeric text causes an error
Errors are the exception to every rule: whether an error value sits in a range
or arrives as a direct argument, the formula fails with that error. Dates are
also just numbers to SUM, as the next example shows.
SUM with Dates
Dates in Excel are stored as serial numbers, so SUM adds them like
any other value. This makes it easy to add days to a date.
| A | B |
|---|---|
| 1/1/2026 | |
| 5 | |
| =SUM(A1:A2) |
=SUM(A1:A2)
The date 1/1/2026 is stored as the serial number 46023, and A2 adds 5 to it, so the formula returns 46028. If the result cell is formatted as a date, it displays as 1/6/2026. Remember to format the result, or you will see a large serial number instead of a date.
SUM with Error Values
If any cell in the SUM range contains an error value, the entire SUM formula returns that error. This example shows this behavior.
| A | B |
|---|---|
| 10 | |
| #N/A | |
| 20 | |
| =SUM(A1:A3) |
The table demonstrates how SUM reacts when encountering error
values in the range. The presence of #N/A in A2 causes the entire SUM to fail.
=SUM(A1:A3)
This formula attempts to sum A1 (10), A2 (#N/A error), and A3 (20). Instead of
a numeric result, it returns #N/A. To sum the remaining numbers while skipping
errors, use AGGREGATE: =AGGREGATE(9, 6, A1:A3)
returns 30.
SUM with Named Ranges
SUM works seamlessly with named ranges, making formulas more readable. This example shows SUM with a named range.
| A | B |
|---|---|
| 100 | |
| 200 | |
| 300 | |
| =SUM(Quarter1) |
The table assumes cells A1:A3 are named "Quarter1". The SUM formula references this named range instead of cell addresses, improving formula clarity.
=SUM(Quarter1)
This formula sums all values in the named range "Quarter1" (A1:A3). The result is 600. Named ranges make formulas more understandable and maintainable.
AutoSum and the Alt+= Shortcut
For quick totals you do not need to type the formula yourself. Place the cursor
below or beside a column of numbers and press Alt+= on Windows, or
click AutoSum on the Home or Formulas tab. Excel inserts a
SUM formula with the nearest contiguous range filled in and shows
a preview of the proposed range.
=SUM(B2:B5)
AutoSum only picks up adjacent cells, so always check that the proposed range covers exactly the rows or columns you expect before pressing Enter.
SUM with Dynamic Arrays
In modern Excel versions, SUM can work with dynamic array formulas. This example demonstrates summing a filtered range.
=SUM(FILTER(A1:A10, B1:B10="Yes"))
This formula sums only values in A1:A10 where corresponding cells in B1:B10
contain "Yes". The FILTER function creates a dynamic array that SUM then
processes. This powerful combination enables conditional summing without
needing SUMIF. FILTER requires Microsoft 365 or Excel 2021 and later; in older
versions, use SUMIF instead.
SUM versus SUMIF, SUMPRODUCT, and SUBTOTAL
SUM has close relatives that solve specific total problems. Choose
the right one based on what you need.
| Function | Use it when |
|---|---|
| SUM | You need a plain total of numbers, ranges, or literals. |
| SUMIF / SUMIFS | You need the total of values meeting a condition, such as
=SUMIF(A1:A10, ">10"). |
| SUMPRODUCT | You need weighted or array-based totals; it handles arrays natively in
every Excel version, such as =SUMPRODUCT(A1:A3, B1:B3). |
| SUBTOTAL / AGGREGATE | You need totals that respect filters and hidden rows;
SUBTOTAL(9, A1:A3) sums visible cells only. |
SUM Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Range contains an error value | The same error is returned | Remove the error, or use AGGREGATE(9, 6, ...) to skip errors |
| Numbers stored as text in cells | Total smaller than expected | Convert with VALUE or NUMBERVALUE; check the green error indicator |
| Direct text argument is not numeric | #VALUE! error | Remove the text or convert it with VALUE |
| Logical values in a range are skipped | Result uses only the numeric cells | Type logicals directly, or coerce them with multiplication |
| Result shows a large number instead of a date | Dates are summed as serial numbers | Format the result cell as a date |
| Hidden or filtered rows change the total | Hidden values are included | SUM includes hidden rows; use SUBTOTAL or AGGREGATE |
| Unexpected result from a 3D reference | Wrong total across sheets | Verify the sheet order in the Sheet1:Sheet3 reference |
The SUM function is essential for all Excel users. From basic
addition to complex multi-sheet calculations, it handles the work efficiently.
Its main subtlety is what it counts: SUM ignores text, logical
values, and empty cells inside ranges, but it converts logical values and
numeric text typed directly into the argument list, and it always includes
zeros and propagates errors. When conditions must be applied, switch to
SUMIF or SUMIFS; when filters and hidden rows matter,
reach for SUBTOTAL or AGGREGATE; when arrays are
involved, SUMPRODUCT works in every version. Mastering these
distinctions will make your totals reliable and easy to explain.
Author
List all Excel Formulas.