#1 Easy Way to Learn Excel SUM Function

Excel SUM Function:

Questions answered in this section:

1. How to create formulas to add numbers in Excel?
2. What is the Excel SUM Function formula?
3. How to add numbers in Excel?
4. How to add entire column in Excel?
5. How add values of entire row in Excel?
6. How to add non-contiguous cells in Excel?
7. How to add non-contiguous ranges in Excel?
8. How to add values from multiple sheets in Excel using Excel SUM Function?
9. How calculate cumulative sum or running sum in Excel?
10. How to use Auto SUM in Excel?

1. How to create formulas to add numbers in Excel?

To professionally work in Microsoft Excel, one has to master the techniques to build complex formulas. Formulas are the heart and soul of a spreadsheet. Excel offers a powerful platform in which one can build complex formulas. You can easily convert Excel into a powerful calculator. In this section, we discuss the fundamentals of Excel formulas. We will discuss some facts that will even be useful for experienced Excel users.

Formula Fundamentals:

• All formulas in Excel begin with an equal (=) sign: The equal sign tells Excel that the characters you will write after the equal sign are part of formula. If you do NOT use the equal sign, Excel may read the characters input as texts. Let’s have an example. In our example, we begin by selecting blank cell D3. Then we type =20+10, and then press ENTER key. We can see the value 30 in cell D3. If you select the cell D3, the formula bar displays the formula =20+10 (as it is in the image below).

• Know the order of operations: “Please Excuse My Dear Aunt Sally” is followed by Excel in mathematical operations. The mathematical operators include plus (+), minus (-), division (/), and multiplication (*). Excel performs the operations in the following order:
  • Excel performs the calculation within parentheses first.
  • Excel performs the calculation of multiplication and division before addition and subtraction.
  • Excel performs the calculation of addition and subtraction after multiplication and division.
  • Final note: Excel performs calculation of consecutive operators with the same level of precedence from left to right. When you have both multiplication and division consecutively, Excel will perform the calculation of the operator that comes first. Let’s see and example.

Formula	Description	Results
=8/2*4	Excel performs the division first (8/2), and then multiples the result (4) by 4.	16
=8*2/4	Excel performs the multiplication first (8*2), and then divides the result (16) by 4.	4
=8-2+4	Excel performs the subtraction first (8-2), and then adds 4 with the result (6)	10
=8+2-4	Excel performs the addition first (8+2), and then subtract 4 from the result (10).	6

• Placement of parentheses: We can change the order of precedence of the operators by using parentheses because Excel performs the calculation within parentheses first. Let see some examples.

Formula	Description	Results
=8/ (2*4)	Excel performs the multiplication first (2*4) since it is within parentheses, and then divides 8 by the result 8.	1
= (8/2) *4	Excel performs the division first (8/2), and then multiplies the result (4) by 4.	16
=8-(2+4)	Excel performs the addition first (2+4), and then subtracts the result (6) from 8.	2
= (8-2) + 4	Excel performs the subtraction first (8-2), and then adds 4 with the result (6)	10

• Cell references in formula: When we use cell references in formula, the formula is linked to the referenced cells. The results depend on the values in the referenced cells and change automatically if the values in the referenced cells are changed. Let’s see an example. In step 1, Select C3, write =20+10, and press enter. Step 2, select D5, write =C3, and press enter. Step 3, select E7, write =C3+D5, and press enter. Excel returns the value 60. Cell references are helpful when we create complex formulas.

• Know the different types of cell references: To know different types of cell references, you can go to the post. Cell reference types.

2. What is the Excel SUM Function Formula:

Introduction:

The SUM function is used to add b values. The SUM function can add individual values, cell references or ranges or mix of all three. You can use the SUM function to add values from the same worksheet as well as from different worksheets. You can also add non-contiguous cells from the same column or from different columns.

=SUM(Argument1, Argument2, ….Argument_n)

Properties of arguments:

1. Argument can be a range, a number, or cell references
2. Each argument must be separated by COMMA (,)
3. Excel SUM function can handle up to 255 individual arguments

3. How to add numbers in Excel:

To add 2 or more numbers in excel, you can simply use the following formula:

=SUM (10,5), and the answer would be 15.

Let’s practice:

Formula	Description	Results
=SUM (10,5)	Adds 10 and 5	15
=SUM (“4”, 10, FALSE, TRUE)	Adds 4, 10, 1, and 1. Logical value “FALSE” and “TRUE” are translated into number 1. Excel has translated only the first logical value, FALSE.	15
=SUM (B2, B5)	Adds -4 and 0.4	-3.6
=SUM (B3, B4, B5)	Adds 10, 20, and 0.4	30.4
=SUM (B2:B5)	Adds values in cells B2 through B5	26.4
=SUM (B3:B4,10)	Adds values in cells B3 through B4, and then adds 10.	40
=SUM (21/3,24/3)	Adds values 21/3 and 24/3	15

4. How to add entire column in Excel:

To add the values of the entire column A, you should simply use formula:

=SUM (A: A) //Where A is the reference of column A//

When we use A:A, we tell excel to sum all the values from A1 to A_n, where n is the last column.

In our example, we want to add all the values of column A; therefore, we used the formula:

=SUM (A:A).

5. How add values of entire row in Excel:

To add the values of entire row in Excel, you should simply use the following formula:

=SUM(2:2) //Where 2 is the reference of row 2//

In our example, we have used the formula to add all the values in row 2. When we write =SUM(2:2), we tell Excel to add all the values in the entire row–from first to last values.

6. How to add non-contiguous cells in Excel:

To add non-contiguous cells in Excel, we need to use the respective references to the cells or ranges. Non-contiguous means not next to each other. Let’s see two examples.

Example 1: Adding values in Non-contiguous cells:

Suppose, we want to add the values in the cells B2, C4, and D6. We need to write the following formula:

=SUM(B2,C4,D6)

The Excel returns the value 50. We can do the same by using the formula =B2+C4+D6 in an empty cell.

Example 2: Adding values in non-contiguous ranges:

Suppose, we want to add the number of apple sold on Tuesday and Wednesday (B3:B4), Banana sold on Thursday and Friday (C5:C6), and Orange sold on Monday and Tuesday (D2:D3). The ranges are not adjacent to each other. To add the values in these ranges, we need to use the range references as shown in the image below.

7. How to add values from multiple sheets:

Sometimes, you need to add the values from different sheets. To do that, you need to understand the basic of the formula.

=SUM(Sheet_NUM!+Range,Sheet_NUM!+Range)

Example:

We want to add the values of the column B from Sheet3 and the values of the column B from sheet2. To do that, we write the following formula:

=SUM(Sheet2!B2:B10,Sheet3!B2:B10)

Know the formula details:

Part 1: “Sheet2! = It tells excel that we want to use the column from Sheet2.

Part 2: “B2:B10” = It tells excel that we want values from this range.

8. How calculate cumulative sum or running sum in Excel:

To calculate the cumulative sum of numbers in a column, follow the steps below:

Step 1: Select an empty cell where you want your cumulative sum.

Step 2: Write =SUM (

Step 3: Write the cell reference, from where you want to start. In our example, we want to start from C6. We write C6.

Step 4: Press F4 on the keyboard. You will see the following:

=SUM ($C$6

Step 5: Write :C6. Press Enter. You are done. The final formula is:

=SUM ($C$6:C6)

9. How to use Auto SUM in Excel:

To know the detail on Auto SUM Excel, you may like to visit the post “Auto SUM in Excel“

More resources:

You may also be interested to know:

1. Excel SUM Function to SUM numbers in a range
2. SUM column range using Name Range.
3. Naming of Range

Happy Exceling!!