×
英语
很抱歉。本文没有你的语言版本。
公式和函数

SUM 函数

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.

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.

This section will discuss some best practices for working with the SUM function. 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.

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 as text, 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 go to 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).

My SUM function shows a whole number, but it should show decimals

Check your cell formatting to make sure that you're displaying decimals. 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, making sure to indicate the number of decimal places you want.

  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, making sure to indicate the number of decimal places you want.

  3. How do I add or subtract Times? You can add and subtract times in a few different ways. For example, to get the difference between 8:00 AM - 12:00 PM for payroll purposes you would use: =("12:00 PM"-"8:00 AM")*24, taking the end time minus the start time. Note that Excel calculates times as a fraction of a day, so you need to multiply by 24 to get the total hours. 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 (8.50 hours total).

    If you're simply adding hours and minutes and want to display that way, then you can sum and don't need to multiply by 24, so in the second example we're using =SUM(A6:C6) since we just need the total number of hours and minutes for assigned tasks (5:36, or 5 hours, 36 minutes).

    Calculating times

    For more information, see: Add or subtract time.

  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.

  5. How do I sum just visible cells? Sometimes, when you manually hide rows or use AutoFilter to display only certain data you also only want to sum the visible cells. You can use the SUBTOTAL function. If you're using a total row in an Excel table, any function you select from the Total drop-down will automatically be entered as a subtotal. See more about how to Total the data in an Excel table.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Learn more about SUM

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

The SUMIFS function adds only the values that meet multiple criteria

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)

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×