SUM functions

Probably the most frequently used function in Excel (or any other spreadsheet program), =SUM does just that: It sums a column, row, or range of numbers—but it doesn’t just sum. It also subtracts, multiplies, divides, and uses any of the comparison operators to return a result of 1 (true) or 0 (false).

You can also get the same results just using the plus (+) sign in place of the function SUM. For example, both of these formulas produce the same answer: =SUM(J7*9) and =+(J7*9). In the spreadsheet graphic below, notice that cells E3 through E8 use the SUM function, while cells E9 through E9 through E14 use the plus (+) sign and the results are the same.

You can enter the SUM function (or + sign) manually or select it from the Ribbon menu under Formulas > Math & Trig (button), then choose from the drop-down list; or choose (from the Ribbon menu) Formulas > Insert Function, then scroll down the list and select it from there.

If you just want to add a single column of numbers, position your cursor in the cell at the bottom of that column, click the AutoSum button > SUM, and press Enter. Excel frames the column of numbers in green borders and displays the formula in the current cell.

The problem comes when the range of numbers you need to calculate gets complicated with multiple calculation operators over multiple cellsFor example: =SUM(H1+I1*J1-M1*J1. Remember your high school math? If the numbers inside the formula are not grouped properly, the answer will be wrong. Notice the screenshot below (figure 2).

Enter the following column headers in H2 through P2 (use Alt+ Enter to stack headers in a single cell): Daily Earnings, Plus Bonuses, Times Days Worked, Gross Pay, (formula), Minus Meals at $9.00 per day, Total Monthly Earnings, Formula, and Comment.

NOTE: The formula columns are FYI only and provide no intrinsic value to the spreadsheet. They just “display” the formula for your benefit (so you can see the syntax of each formula used).

For this exercise, you can enter the same values in H3:11, I3:11, and J3:11, with or without the blank rows in between (again, added for easier viewing). Complete as follows: $86.00, $20.00, 22.0 workdays, and the rest are formulas. Note that as we build each formula, we are combining the steps, eventually, into a single formula.

We start out with three separate formulas. The first is to add the Daily earnings, plus Bonuses, multiplied by the number of days worked in a month, which equals Gross Pay: =SUM(H3+I3*J3) in cell K3. Notice that the answer is $526.00. That just doesn’t look right.

Use your calculator to check the formulas to ensure they’re correct BEFORE you copy them to the rest of the cells in the column.

The formula in K3 is wrong. It requires grouping the numbers according to the order of calculation using commas or parentheses.

Note the corrected formula in cell K4: =SUM(H4+I4)*J4. Check your numbers again (with your calculator) and note that this formula is correct. The correct answer is $2,332.00.

5. The second formula (in M4) is =SUM(J4*9) multiplies the workdays (22) times $9.00, the cost of meals per day. The correct answer is $198.00.

6. The third formula (in N4) calculates the monthly earnings minus the meals: =SUM(K4-M4); answer is $2,134.00.

7. In the next group (H6:N8), the formulas in M6:M8 remain the same: =SUM(J7*9), etc.—again that’s the number of workdays times the cost of meals. But the formulas in column K are eliminated and then combined with the formulas in column M: =SUM(H7+I7)*J7-M7. Note that the syntax (the structure or layout of the formula) is correct in cells N7 and N8, but incorrect in N6.

8. The next group (H10:H11) combines the formulas in column M with the formulas in column N: =SUM(H11+I11)*J11-(M11*J11)—note that the formula in N10 is incorrect. By combining these formulas into one, you can eliminate columns K and L.

9. Also, instead of “hardcoding” the price of the meals (as shown in M3:M4 and M6:M8), you can now change the price of the meals in column M (M10:M11) when inflation dictates an increase instead of changing the formula.