Problem: My manager doesn't know the trick for doing automatic subtotals. He manually entered blank lines between each customer and entered SUM formulas for each customer. How can I produce a grand total of all customers?
- With 100 customers, the formula would be lengthy.
Strategy: Sum all of the cells and divide by 2 using =SUM(D2:D20)/2.
This method works! It is an old accounting trick (taught to me by an old accountant).
It is not intuitive, especially if you hated algebra.
Every number is in the Grand Total twice, once from the detail row and once from the manual totals.
Try it for yourself a few times, comparing the results to the method of using =D19+D13+D7. You will see that you get the same result.
- Sum and divide by 2.
Gotcha: This method works only if all the customers are totaled. A manager who doesn't know how to use subtotals might be the kind of manager who doesn't total the customers with only one detail line. Below, line 9 will cause the total to not work.
- If someone is manually adding totals, he might not add a redundant total for row 9.