Fill in the blank: when describing a sum function, the _____ is =sum(value 1 through value 2).

Sums up cells that are supplied as multiple arguments

The SUM Function[1] is categorized under Excel Math and Trigonometry functions. The function will sum up cells that are supplied as multiple arguments. It is the most popular and widely used function in Excel.

SUM helps users perform a quick summation of specified cells in MS Excel. For example, we are given the cost of 100 items bought for an event. We can use the function to find out the total cost of the event.

Formula

=SUM(number1, [number2], [number3]……)

The SUM function uses the following arguments:

1. Number1 (required argument) – This is the first item that we wish to sum.
2. Number2 (required argument) – The second item that we wish to sum.
3. Number3 (optional argument) – This is the third item that we wish to sum.

The function sums values supplied as arguments (up to 255 arguments). Arguments can be supplied as numbers, cell references, ranges, arrays, constants, and the results of other formulas or functions.

How to use the SUM Function in Excel?

To understand the uses of the SUM function, let’s consider a few examples:

Example 1

Suppose we are given the following data:

We wish to find out the total sales for the first six months. The formula to be used is:

We get the result below:

Example 2

Generally, the SUM function is used as part of bigger formulas used in complex calculations. Suppose we are given the following data:

As seen above, there is missing information in the data. In such a case, we can use the SUM function along with the IF function to show a warning message. The formula to be used is:

We get the result below:

Things to remember about the SUM Function

1. #VALUE! error – Occurs when the criteria provided is a text string that is more than 255 characters long.
2. The easiest way to apply the function is to simply select a cell next to the numbers that we wish to add and click on AutoSum on the Home tab. We can then press the Enter key and the SUM formula is automatically inserted.

We can also find AutoSum in the Formulas tab:

The AutoSum feature not only enters a SUM formula but also selects the most likely range of cells that we wish to add up.

Click here to download the sample Excel file

Additional Resources

Thanks for reading CFI’s guide to the Excel SUM function. By taking the time to learn and master these Excel functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

Article Sources

Excel for Microsoft 365 Excel for the web Excel 2021 Excel 2019 Excel 2016 Excel 2013 More...Less

The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three.

For example:

• =SUM(A2:A10) Adds the values in cells A2:10.

• =SUM(A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10.

Syntax:

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

Argument name

Description

Best Practices with SUM

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.

2. #VALUE! errors from referencing text instead of numbers

   If you use a formula like:

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

   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.

3. #REF! error from deleting rows or columns

   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.

4. Formulas won't update references when inserting rows or columns

   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.

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.

Frequently Asked Questions

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).

   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.

   For more ways to work with dates see: Calculate the difference 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 Communityor get support in the Answers community.

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

Math & Trig functions

Excel functions (alphabetical)

Excel functions (by Category)