1

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.

0

Figured it out. Here is what I did.

  1. Added a column called "Row Number" which had the formula "=ROW(1:1)" and it auto copied down.
  2. 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.

1
  • 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 '13 at 20:18
0

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).

0

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.