How do I calculate a running total in an Excel 2007/2010 table object? If you don't know what a table object in Excel is please DO NOT respond with a "=[the cell above] + [the cell adjacent]" type formula as I have found no way to actually do this in a table.
Add a comment
|
3 Answers
Figured it out. Here is what I did.
- Added a column called "Row Number" which had the formula "=ROW(1:1)" and it auto copied down.
- Created a new column called "Number Attrited YTD" and set formula to "=SUMIF([Row Number],"<="&ROW([@[Number Attrited]])-1,[Number Attrited])"
This worked as expected. If the data gets resorted it will throw off a YTD count but my data stays sorted by month so this is not an issue.
-
While this solution works, be aware that it will get really slow on big tables, as every SUMIF will look into the full table column... Mar 29, 2013 at 20:18
Seems simpler to use something like =SUM(x$y:xy) where x represents the column to be accumulated and y the row of the start of data in the table (and where formula should be placed).
The formula you provide in your question can work of you use a named range! Insert a named range with the name manager: Say you have selected A2, go to the Name Manager and insert a new name. Call it CellAbove, scope it to your current worksheet and provide the formula =A1 (make sure that you have no $!).
Now you can enter the follow formula: =IF(ISTEXT(CellAbove),0,CellAbove+[@adjacentCell])!
Of course this also works without the name, only replace CellAbove with the normal, non-table way reference for the cell above, eg E1, when you enter the formula in E2...
answered Mar 29, 2013 at 20:23
