Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 19 '11 at 10:17

This question came from our site for professional and enthusiast programmers.

3 Answers 3

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.

share|improve this answer
    
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... –  Peter Albert Mar 29 '13 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).

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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