How To Use Excel SUM Function

It’s an expedient way to add the values within a column or row or even non-adjacent cells. SUM is found within the Functions Tab under Math & Trig; scroll down the menu and select SUM. AutoSum is also located as it owns a button in the Functions Tab (highlighted).

The third location for the SUM Function is in the Home Tab in the editing box. The fact that Excel has the SUM function in so many locations gives a hint as to how often this Function is used.

The SUM Function is written as = SUM (number1, number2…) the numbers can be manually entered (4), they can be a cell (A2), or they can be a range (A3:A6) or a combination of all three (4, A2, A3:A6).

SUM

The above shows the four ways to easily use the SUM Function at the end of each row to the total expense.

1. Individually – In G2, enters the SUM Function and manually enters all numbers within Row 2, close the parentheses, and hit enter.
2. Cell by Cell – In G3, the SUM Function is entered, and each numeric cell is selected in Row 3, separated by commas.
3. Range – G4 shows the Sum of a range of cells. Using a range of cells is best if additional columns will be added because Excel will automatically adjust the Range to reflect any new columns. Individually entering the data and using the cell by cell method will not include new columns in their formulas.
4. AutoSum – When a range of data is selected as we did in Row 6, the Quick Sum box will appear. Select Totals and click Sum. The first Sum within the Quick Sum box shows the blue rows at the bottom, meaning it will total a column and the circled Sum shows the yellow column on the right for totaling rows. We are totaling each row, so in our example, the circled Sum would be selected.

Non-Adjacent Cells

To add all Travel Expenses (Food, Gas, and Hotels), enter = SUM (B2, B4, B6) as the Cells are not next to each other and cannot be selected by using the Range method.

Using Mathematical Equations

Adding to the end of the beginning of the SUM Function, you can create mathematical equations. The SUM Function can be added, subtracted, multiplied, and divided by any other numbers. Examples of each are listed in Column H, with the Results shown in Column G below.

Using The SUM With Other Functions

The SUM Function can be nested within other functions or can be used along with other functions.

Nesting in an IF Function Example: The boss is offering a reimbursement if the total expenses are greater than $2000 for the year.

1. In B9 enter = IF (SUM (B2:B8)>2000,”yes”, “no”)
2. That Function is saying if the SUM of column B is greater than 2000, the result is yes, it is eligible for reimbursement; otherwise, enter a no.
3. Copy across all columns to apply to each Realtor.

SUM and another function Example:

To find the total average expenses for budgeting, each row could be averaged and then calculate the total of those averages, or all can be entered into one Function as follows:

=SUM (AVERAGE (B2:F2), AVERAGE (B3:F3), AVERAGE (B4:F4), AVERAGE (B5:F5), AVERAGE (B6:F6), AVERAGE (B7:F7), AVERAGE (B8:F8))

Nesting the AVERAGE Function within the SUM Function will return the same value without adding a Column into our worksheet.