|
by TheExcelAddict.com How To Create Cumulative TotalsWhat's the best way to create a column that stores cumulative totals? Let's say you have a data table. In Column A you list the days of the month, 1 to 31. In Column B you enter your daily totals (i.e. sales, rainfall, etc...). Assuming Row 1 is used for column headings, your daily totals will be entered in cells B2:B32. You want to keep a running month-to-date total in column C. Here is a really easy solution that you can use: Using the scenario above, you enter =SUM($B$2:B2) in cell C2, and copy that formula down column C. Note the dollar signs in the first cell reference in the formula ($B$2) and no dollar signs in the second cell reference (B2). The dollar signs indicate an "absolute reference". This means that when you copy this formula down column C, the reference to $B$2 won't change. The second cell reference to B2 doesn't have dollar signs, so that means it is a "relative reference" and will adjust as it is copied. When the formula is copied to cell C3, the formula becomes = SUM($B$2:B3) and the result will be the sum of the range B2:B3. As you enter values in column B, the formulas in column C will show the cumulative totals. After you have copied these formulas down column C, you will notice that the cumulative totals in the rows below the last value in column B all show the same cumulative total. To avoid showing cumulative totals for the rows that have no value entered in column B, you will need to modify the formula I just gave you. Go back to cell C2 and enter this formula instead and copy it down column C as far as C32. The formula in cell C2 will be =IF(B2="","",SUM($B$2:B2)) This says, if cell B2 is blank then don't put anything in cell C2, otherwise add a cumulative total formula. Now, as you enter the amounts in Column B for each day, the cumulative total will appear next to each amount. If you would like to see a sample spreadsheet of this tip, you can find it here.
|