SUM function

The SUM function, one of the math and trig functions, adds values. You can add individual values, cell references or ranges or a mix of all three.

For example:

  • =SUM(A2:A10)

  • =SUM(A2:A10, C2:C10)

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

This video is part of a training course called Add numbers in Excel 2013.

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

Argument name

Description

number1    (Required)

The first number you want to add. The number can be like 4, a cell reference like B6, or a cell range like B2:B8.

number2-255    (Optional)

This is the second number you want to add. You can specify up to 255 numbers in this way.

If you want to quickly get the Sum of a range of cells, all you need to do is select the range and look in the lower right-hand side of the Excel window.

Screen shot of selecting a range of cells, then looking in the Status Bar
Status Bar

This is the Status Bar, and it displays information regarding whatever you have selected, whether it's a single cell or multiple cells. If you right-click on the Status Bar a feature dialog box will pop out displaying all of the options you can select. Note that it also displays values for your selected range if you have those attributes checked. Learn more about the Status Bar.

The easiest way to add a SUM formula to your worksheet is to use the AutoSum Wizard. Select an empty cell directly above or below the range that you want to sum, and on the Home or Formula tabs in the Ribbon, press AutoSum > Sum. The AutoSum Wizard will automatically sense the range to be summed and build the formula for you. It can also work horizontally if you select a cell to the left or right of the range to be summed. Note it’s not going to work on non-contiguous ranges, but we'll go over how to do that.

You can use the AutoSum Wizard to automatically build a Sum formula.  Select a range above/beneath or left/right of the range to be summed and goto the Formula tab on the Ribbon, then select AutoSum & SUM.
Use the AutoSum Wizard to quickly sum contiguous ranges

The AutoSum dialog also lets you select other common functions like:

  • Average

  • Count numbers

  • Max

  • Min

  • More functions

    AutoSum vertically

    Cell B6 shows the AutoSum Sum formula: =SUM(B2:B5)
    AutoSum vertically

The AutoSum Wizard has automatically detected cells B2:B5 as the range to be summed. All you need to do is press Enter to confirm it. If you need add/exclude more cells, you can hold the Shift Key > Arrow key of your choice until your selection matches what you want, and press Enter when you're done.

Intellisense function guide: the SUM(number1,[number2], …) floating tag beneath the function is its Intellisense guide. If you click the SUM or function name, it will turn into a blue hyperlink, which will take you to the Help topic for that function. If you click the individual function elements, their representative pieces in the formula will be highlighted. In this case only B2:B5 would be highlighted since there is only one number reference in this formula. The Intellisense tag will appear for any function.

AutoSum horizontally

Cell D2 shows the AutoSum Sum formula: =SUM(B2:C2)
AutoSum horizontally

Using SUM with non-contiguous ranges.  Cell C8's formula is =SUM(C2:C3,C5:C6). You could also use Named Ranges, so the formula would be =SUM(Week1,Week2).
Sum non-contiguous cells

The AutoSum Wizard will generally only work for contiguous ranges, so if you have blank rows or columns in your sum range, Excel is going to stop at the first gap. In that case you’d need to SUM by selection, where you add the individual ranges one by one. In this example if you had data in cell B4, Excel would generate =SUM(C2:C6) since it would recognize a contiguous range.

You can quickly select multiple, non-contiguous ranges with Ctrl+LeftClick. First, enter “=SUM(“, then select your different ranges and Excel will automatically add the comma separator between ranges for you. Press enter when you’re done.

TIP: you can use ALT+= to quickly add the SUM function to a cell. Then all you need to do is select your range(s).

Note: you may notice how Excel has highlighted the different function ranges by color, and they match within the formula itself, so C2:C3 is one color, and C5:C6 is another. Excel will do this for all functions, unless the referenced range is on a different worksheet or in a different workbook. For enhanced accessibility with assistive technology, you can use Named Ranges, like “Week1”, “Week2”, etc. and then reference them in your formula:

=SUM(Week1,Week2)

This section will discuss some best practices for working with the SUM functions. Much of this can be applied to working with other functions as well.

The =1+2 or =A+B Method – While you can enter =1+2+3 or =A1+B1+C2 and get fully accurate results, these methods are error prone for several reasons:
  1. Typos – Imagine trying to enter more and/or much larger values like this:

    • =14598.93+65437.90+78496.23

    Then try to validate that your entries are correct. It’s much easier to put these values in individual cells and use a SUM formula. In addition, you can format the values when they’re in cells, making them much more readable then when they’re in a formula.

    Use the SUM function instead of hard-coding values in formulas.  Formula in cell D5 is =SUM(D2:D4)
  2. #VALUE! errors from referencing text instead of numbers

    If you use a formula like:

    • =A1+B1+C1 or =A1+A2+A3

    Example of poor formula construction.  Formula in cell D2 is =A2+B2+C2

    Your formula can break if there are any non-numeric (text) values in the referenced cells, which will return a #VALUE! error. SUM will ignore text values and give you the sum of just the numeric values.

    Proper formula construction.  Instead of =A2+B2+C2, cell D2's formula is =SUM(A2:C2)
  3. #REF! error from deleting rows or columns

    #REF! error caused by deleting a column.  Formula has changed to =A2+#REF!+B2

    If you delete a row or column, the formula will not update to exclude the deleted row and it will return a #REF! error, where a SUM function will automatically update.

    SUM function will automatically adjust for inserted or deleted rows and columns
  4. Formulas won't update references when inserting rows or columns

    =A+B+C formulas won't update if you add rows

    If you insert a row or column, the formula will not update to include the added row, where a SUM function will automatically update (as long as you’re not outside of the range referenced in the formula). This is especially important if you expect your formula to update and it doesn’t, as it will leave you with incomplete results that you might not catch.

    Example portrays a SUM formula automatically expanding from =SUM(A2:C2) to =SUM(A2:D2) when a column was inserted
  5. SUM with individual Cell References vs. Ranges

    Using a formula like:

    • =SUM(A1,A2,A3,B1,B2,B3)

    Is equally error prone when inserting or deleting rows within the referenced range for the same reasons. It’s much better to use individual ranges, like:

    • =SUM(A1:A3,B1:B3)

    Which will update when adding or deleting rows.

  1. Let’s say you want to apply a Percentage Discount to a range of cells that you’ve summed.

    Using Operators with SUM.  Formula in cell B16 is =SUM(A2:A14)*-25%.  The formula would be constructed properly if -25% was a cell reference instead, like =SUM(A2:A14)*E2
    • =SUM(A2:A14)*-25%

    Would give you 25% of the summed range, however that hard-codes the 25% in the formula, and it might be hard to find later if you need to change it. You’re much better off putting the 25% in a cell and referencing that instead, where it’s out in the open and easily changed, like this:

    • =SUM(A2:A14)*E2

    To divide instead of multiply you simply replace the “*” with “/”: =SUM(A2:A14)/E2

  2. Adding or Subtracting from a SUM

    i. You can easily Add or Subtract from a Sum using + or - like this:

    • =SUM(A1:A10)+E2

    • =SUM(A1:A10)-E2

  1. You can absolutely use SUM with other functions. Here’s an example that creates a monthly average calculation:

    Using SUM with other functions.  Formula in cellM2 is =SUM(A2:L2)/COUNTA(A2:L2) .  Note: columns May-November are hidden for clarity.
    • =SUM(A2:L2)/COUNTA(A2:L2)

  2. Which takes the SUM of A2:L2 divided by the count of non-blank cells in A2:L2 (May through December are blank).

  1. Sometimes you need to sum a particular cell on multiple worksheets. It may be tempting to click on each sheet and the cell you want and just use “+” to add the cell values, but that’s tedious and can be error prone.

    • =Sheet1!A1+Sheet2!A1+Sheet3!A1

    You can accomplish this much easier with a 3D or 3-Dimensional SUM:

    3D Sum - Formula in cell D2 is =SUM(Sheet1:Sheet3!A2)
    • =SUM(Sheet1:Sheet3!A1)

    Which will sum the cell A1 in all sheets from Sheet 1 to Sheet 3.

    This is particularly helpful in situations where you have a single sheet for each month (January-December) and you need to total them on a summary sheet.

    3d SUM across Named Sheets.  Formula in D2 is =SUM(January:December!A2)
    • =SUM(January:December!A2)

    Which will sum cell A2 in each sheet from January through December.

    Notes: If your worksheets have spaces in their names, like “January Sales”, then you need to use an apostrophe when referencing the sheet names in a formula. Notice the apostrophe BEFORE the first worksheet name, and again AFTER the last.

    • =SUM(‘January Sales:December Sales’!A2)

    The 3D method will also work with other functions like AVERAGE, MIN, MAX, etc:

    • =AVERAGE(Sheet1:Sheet3!A1)

    • =MIN(Sheet1:Sheet3!A1)

    • =MAX(Sheet1:Sheet3!A1)

Problem

What went wrong

My SUM function shows #####, not the result.

Check your column widths. ##### generally means that the column is too narrow to display the formula result.

My SUM function shows the formula itself, not the result.

Check that the cell isn't formatted as text. Select the cell or range in question and use Ctrl+1 to bring up the Format Cells dialog, then click the Number tab and select the format you want. If the cell was formatted as text and doesn't change after you change the format, you might need to use F2 > Enter to force the format to change.

My SUM function doesn't update.

Make sure that Calculation is set to Automatic. On the Formula tab goto Calculation Options. You can also use F9 to force the worksheet to calculate.

Make sure that calculation is set to Automatic.  From the Formula tab goto Calculation Options.

Some values aren't added.

Only numeric values in the function reference or range can be added. Empty cells, logical values like TRUE, or text are ignored.

The #NAME? error appears instead of the expected result.

This usually means that the formula is misspelled, Like =sume(A1:A10) instead of =SUM(A1:A10).

  1. I just want to Add/Subtract/Multiply/Divide numbers - See this video series on Basic Math in Excel, or Use Excel as your calculator.

  2. How do I show more/less decimal places? - You can change your number format. Select the cell or range in question and use Ctrl+1 to bring up the Format Cells Dialog, then click the Number tab and select the format you want.

  3. How do I sum Times? You can add and subtract times. For example to get the difference between 8:00 AM - 12:00 PM you would use: =("12:00 PM"-"8:00 AM")*24. Note that Excel calculates times as a fraction of a day, so you need to multiply by 24 to get the total hours. If you're adding hours, like the number of hours on assigned tasks you can sum and don't need to multiply by 24. In the first example we're using =((B2-A2)+(D2-C2))*24 to get the sum of hours from start to finish, less a lunch break. In the second example we're using =SUM(A6:C6) since we just need to total the number of hours.

    Calculating times
  4. How do I get the difference between dates? As with times, you can add and subtract dates. Here's a very common example of counting the number of days between two dates. It's as simple as =B2-A2. The key to working with both Dates and Times is that you start with the End Date/Time and subtract the Start Date/Time.

    Calculating differences between dates

    For more ways to work with dates see: Calculate the number of days between two dates.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See Also

The SUMIF function adds only the values that meet a single criteria

The SUMIFS function adds only the values that meet multiple criteria

The SUMSQ function sums multiple values after it performs a mathematical square operation on each of them

See a video on how to use the SUM Function

The COUNTIF function counts only the values that meet a single criteria

The COUNTIFS function counts only the values that meet multiple criteria

Overview of formulas in Excel

How to avoid broken formulas

Find and correct errors in formulas

Keyboard shortcuts in Excel for Windows

Keyboard shortcuts in Excel for Mac

Math & Trig functions

Excel functions (alphabetical)

Excel functions (by Category)

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×