The SUM Function is 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 its own 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.if you want to read cell locking in excel you can read it at https://sgrdimsr.org/lock-cells-excel/
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).
Looking at the above, it show the four ways to easily use the SUM Function at the end of each row to total each expense
- Individually – In G2 enter the SUM Function and manually enter all numbers within Row 2, close the parentheses and hit enter.
- Cell by Cell – In G3 the SUM Function is entered and each numeric cell is selected in Row 3 separated by commas
- 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.
- 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.
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 or 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.
- In B9 enter =IF(SUM(B2:B8)>2000,”yes”,”no”)
- 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.
- 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:
Nesting the AVERAGE Function within the SUM Function will return the same value without having to add a Column into our worksheet.