Download FREE SUM Function Excel Template and Follow Along!
SUM Function Excel Template.xlsx

Table Of Contents

arrow

SUM Function in Excel

Publication Date :

Blog Author :

Edited by :

The Syntax of the SUM Excel Function

The syntax of the function is shown in the following image:

SUM Formula

The function accepts the following arguments:

  • Number1: This is the first numeric value to be added.
  • Number2: This is the second numeric value to be added.

The “number1” argument is required while the subsequent numbers (“number 2”, “number 3”, etc.) are optional.

The Procedure to Enter the SUM Function in Excel

To enter the SUM function manually, type “=SUM” followed by the arguments.

The alternative steps to enter the SUM excel function are listed as follows:

  1. In the Formulas tab, click the “math & trig” option, as shown in the following image.
sum function first step

2. From the drop-down menu that opens, select the SUM option.

sum function seccond step

3. In the “function arguments” dialog box, enter the arguments of the SUM function. Click “Ok” to obtain the output.

sum function third step

The AutoSum Option in Excel

The AutoSum option is the fastest way to add numbers in a range of cells. It automatically enters the SUM formula in the selected cell.

Let us work on an example to understand the working of the AutoSum option. We want to sum the list of values in A2:A7, shown in the succeeding image.

The steps to use the AutoSum command are listed as follows:

  1. Select the blank cell immediately following the cell to be summed up. Choose the cell A8.


    Autosum Option - Step 1

  2. In the Home tab, click “AutoSum”. Alternatively, press the shortcut keys “Alt+=” together and without the inverted commas.


    auto Sum option in excel 1

  3. The SUM formula appears in the selected cell. It shows the reference of the cells that have been summed up.


    Autosum Option - Step 3

  4. Press the “Enter” key. The output appears in cell A8, as shown in the following image.


    auto Sum option in excel 3

How to Use the SUM Function in Excel?

Let us consider a few examples to understand the usage of the SUM function. The examples #1 to #5 show an image containing a list of numeric values.

Example #1

We want to sum the cells A2 and A3 shown in the succeeding image.

Apply the formula “=SUM(A2, A3).” It returns 20.7 in cell C2.

SUM Example 1

Example #2

We want to sum the cells A3, A5, and the number 45 shown in the succeeding image.

Apply the formula “=SUM (A3, A5, 45).” It returns 58.8 in cell C2.

SUM Example 2

Example #3

We want to sum the cells A2, A3, A4, A5, and A6 shown in the succeeding image.

Apply the formula “=SUM (A2:A6).” It returns 135.4 in cell C2.

SUM Example 3

Example #4

We want to sum the cells A2, A3, A5, and A6 shown in the succeeding image.

Apply the formula “=SUM (A2:A3, A5:A6).” It returns 35.4 in cell C2.

SUM Example 4

Example #5

We want to sum the cells A2, A3, A5, A6, and the number 500 shown in the succeeding image.

Apply the formula “=SUM (A2:A3, A5:A6, 500).” It returns 535.4 in cell C2.

SUM Example 5

The Usage of the SUM Excel Function

The rules governing the usage of the function are listed as follows:

  • The arguments supplied can be numbers, arrays, cell references, constants, ranges, and the results of other functions or formulas.
  • While providing a range of cells, only the first range (cell1:cell2) is required.
  • The output is numeric and represents the sum of values supplied.
  • The arguments supplied can go up to a total of 255.

Note: The SUM excel function returns the “#VALUE!” error if the criterion supplied is a text string longer than 255 characters.

The Limitations of the SUM Function in Excel

The drawbacks of the function are listed as follows:

  • The cell range supplied must match the dimensions of the source.
  • The cell containing the output must always be formatted as a number.

The Nesting of the SUM Excel Function

The built-in formulas of Excel can be expanded by nesting one or more functions inside another function. This permits multiple calculations to take place in a single cell of the worksheet.

The nested function acts as an argument of the main or the outermost function. Excel calculates the innermost function first and then moves outwards.

For example, the following formula shows the SUM function nested within the ROUND function:

Nesting of Sum Excel

For the given formula, the output is calculated as follows:

  • First, the sum of the values in cells A1 to A6 is computed.
  • Next, the resulting number is rounded to three decimal places.

With Microsoft Excel 2007, nested functions up to 64 levels are permitted. Prior to this version, one could nest functions only till 7 levels.

Frequently Asked Questions (FAQs)

1

1. Define the SUM function of Excel.

Arrow down filled
2

2. How to sum the values of filtered data in Excel?

Arrow down filled
3

3. State the benefits of using the SUM function in Excel.

Arrow down filled