I received an email query from a reader who wanted to know how to get Microsoft Excel to perform a specific task. He'd exported a report from QuickBooks that had subtotals for various categories in a single column. He wanted to know whether there's an easy way to get the total of all the individual items in the same column, instead of creating a second column, copying the subtotals, and then adding the amounts in the second column.
There sure is, and I use this trick when I have many columns of data and I don't want to create new columns and copy amounts in order to sum the subtotals.
For the columns that have subtotals, I go to the row below the last data row and enter the following formula: =SUM(Xy:Xz)/2, where X is the column, y is the first row in the calculation and z is the last row in the calculation.
Can you figure out why this works?