- Excel Book Excerpt
Excel Enter a Grand Total of Data Manually Subtotaled
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Enter a Grand Total of Data
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?
Figure 659 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.
Figure 660 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.
Figure 661 If someone is manually adding totals, he might not add a redundant total for row 9.
For more resources for Microsoft Excel: