Basic Formula Series: The Excel® SUM Formula

Key Takeaways

The Excel SUM formula adds values from cell references, ranges, constants or a mix of all three using the syntax =SUM(number1, [number2], ...).
You can sum a continuous range with a colon (e.g., =SUM(A2:A10)) or individual cells separated by commas (e.g., =SUM(A2, A5, A8)).
Use the AutoSum feature or the keyboard shortcut Alt + (plus sign) to insert a SUM formula instantly.
Common SUM errors like #VALUE! error are usually caused by text in cells or incorrect references and are easy to fix.

What Is the Excel SUM Formula?

The SUM function in Excel is one of the most commonly used basic Excel formulas. It adds any specified arguments and values together, making it the fastest way to add numbers in a spreadsheet. Whether you need to total a column of sales figures for data-driven decision making, combine values from scattered cells or mix cell references with constants, SUM handles it all.

Excel® formulas have their own logic and syntax, much like math problems. Once you understand the syntax, you can save a great deal of time by building basic and complex formulas to do the majority of your work for you. Where in math problems you combine numbers with variables, in Excel formulas and functions you combine numbers with cell addresses.

Once a formula is entered, only the result of the formula's calculation appears. Clicking on the cell reveals the formula behind the cell's value in the formula bar.

SUM Function Syntax and Arguments

Here is the syntax for the Excel SUM formula:

SUM(number1,[number2],...)

"SUM" is the name of the function while "number1" and "number2" here are the variables. Arguments can include:

Cell references (e.g., A1, B5)
Ranges (e.g., A1:A10)
Arrays
Constants (e.g., 15, 100)
Results from other calculations and formulas you have set up

The table below shows common Excel SUM formula examples and what each one does:

Formula	What it does
=SUM(A1:A10)	Adds all values in cells A1 through A10
=SUM(A1, B1, C1)	Adds the values in three specific cells
=SUM(A1:A10, 25)	Adds all values in the range plus the constant 25
=SUM(A1:A10, C1:C10)	Adds two separate ranges together

Use a colon to define a continuous Excel SUM range (e.g., D5:D9). Use commas to reference individual cells (e.g., F5, F7, F9).

If you type "=SUM(D5:D9)" into the Formula Bar, the numbers in that range are added together and the value is displayed in the cell you have built your formula in.

If you type "=SUM(F5, F7, F9)" into the Formula Bar, then the calculation adds the figures for those cell references.

How to Use the Excel SUM Formula

Select the cell where you want the calculated total to appear.
Click the Formula Bar to place your cursor there.
Type your SUM formula in the Formula Bar:

a. An "=" must begin the formula

b. Type the first value in your formula, whether that is a cell coordinate, a range, or a number.

c. Type a comma to separate values.

d. Type the second value in the formula.

In this example, the formula adds the number in the cell reference A4, the range of numbers between A7 and A10, then adds the constant 15.

Press the ENTER key on your keyboard to see the result.

Have a simple series of values to add and want to skip a few steps? Place your cursor in the cell you wish for the calculation to be displayed, then select the AutoSum button. The action selects the range of cells that Excel thinks you want to add. Hit enter if Excel guessed right.

You can find AutoSum easily in two places.

On the Home tab in the Editing group.
On the Formulas tab, in the Function Library group.

Excel SUM Formula Shortcut

Forget locating the AutoSum button or typing in the formula bar. Instead press and hold the Alt key on your keyboard and the plus (+) sign. This is one of many time-saving Excel shortcuts that can speed up your daily workflow.

Common SUM Formula Errors and How to Fix Them

Even a simple Excel SUM formula can return unexpected results. Here are the most frequent issues and how to resolve them:

#VALUE! error: This appears when SUM encounters text or special characters in cells it expects to contain numbers. Fix it by removing the text, converting it to a number or using SUMPRODUCT instead.
Numbers stored as text: Cells may look like they hold numbers but are actually formatted as text. Look for the small green triangle in the corner of the cell. Click the warning icon and select "Convert to Number" to fix the issue.
Circular reference warning: This occurs when your SUM formula references its own cell. Fix it by moving the formula to a different cell that falls outside the range you are adding.
Missing cells in a range: When you insert rows or columns outside the SUM range, the new data gets excluded from the total. Fix this by expanding the range or using structured table references that adjust automatically.
Unexpected zero result: This is often caused by referencing the wrong sheet or pointing to empty cells. Double-check your cell references and confirm you are looking at the correct worksheet.

Related Excel Functions to Know

Once you are comfortable with SUM, these related functions can help you work with data more efficiently:

SUMIF: Adds values that meet a single condition. For example, =SUMIF(B2:B10, ">100") totals only values greater than 100.
SUMIFS: Adds values that meet multiple conditions. This is useful when you need to filter by date range and category at the same time.
SUMPRODUCT: Multiplies corresponding values in arrays and returns the sum of the products. It is a powerful alternative when you need weighted totals or Excel finance formulas like PV and FV.
AVERAGE: Calculates the mean of a range. While SUM gives you the total, AVERAGE tells you the typical value.

Build Your Excel Skills with Pryor Learning

Mastering the SUM function is a foundational step toward working confidently in Excel. From basic formulas to advanced data analysis, Pryor Learning offers live and On-Demand Excel training designed for every skill level. Explore Excel courses today to sharpen your spreadsheet skills.

Commonly Asked Questions

To write a basic SUM formula, type =SUM( followed by the cells or range you want to add, then close the parenthesis and press Enter. For example, =SUM(A1:A10) adds all values in cells A1 through A10. You can also list individual cells separated by commas, such as =SUM(A1, B3, C5).

SUM is the function you type manually (e.g., =SUM(B2:B8)), while AutoSum is a built-in Excel feature that automatically inserts the SUM formula for a detected range of adjacent cells. AutoSum is accessed from the Home tab, the Formulas tab or by pressing Alt + (plus sign).

An Excel SUM formula returns zero when the referenced cells are empty, contain text formatted as numbers or point to the wrong range. Check that your cell references are correct and that the values are stored as actual numbers, not text. Look for the green triangle indicator in cell corners as a sign of text-formatted numbers.

Yes, you can sum cells across multiple sheets by using a 3D reference, such as =SUM(Sheet1:Sheet3!A1), which adds cell A1 from Sheet1 through Sheet3. You can also reference individual sheets separated by commas, like =SUM(Sheet1!A1, Sheet2!A1).

SUM adds all values in a specified range, while SUMIF adds only the values that meet a condition you define. For example, =SUMIF(B2:B10, ">100") adds only values greater than 100 in that range. Use SUM for straightforward totals and SUMIF when you need to filter your results.

The seven most commonly used basic Excel formulas are SUM, AVERAGE, COUNT, MIN, MAX, IF and VLOOKUP (or its modern replacement, XLOOKUP). Each serves a different purpose, from adding and averaging values to looking up data across tables. Mastering these gives you a strong foundation for nearly any spreadsheet task.