# A sum formula with fixed lenght

January 25, 2011 at 03:48:04
Specs: Windows Vista, 2GB
 Hi everyone,Im creating a control worksheet in Excel that calculates the sum of say, the last 8 cells in one row. (A1:H1). Lets say the value in the cells is 1,2,3 etz so the value in the summery cell is 36.That the easy part (for me that is)So now i need to enter 1 column every day. But i want to keep the value in the summery cell that of the last 8 days.So after adding 1 column i want the sum of (B1:I1) = 44The real case is much bigger but this is the gist of it. Would apleasiate any help or idear available. Thank you in advance.S

See More: A sum formula with fixed lenght

#1
January 25, 2011 at 06:38:26
 For Row 1, try this. It will only work once you have values in at least A1:H1 since it needs 8 values, as you requested. (0 would be considered a value, but a blank cell would not.)=SUM(INDIRECT(ADDRESS(1,COUNTA(1:1)-7)&":"&ADDRESS(1,COUNTA(1:1))))Change all of the 1's if you need it to work for other Rows.You may need to play with the arguments for the ADDRESS function to get it work for your specific situation, but the concept should be valid for any range.If you need help modifying the formula, come on back with specific ranges and where you want the formula to be placed and we'll see what we can do.Note: Look up the ROW() and COLUMN() functions in Excel Help. That might give you some ideas as to how to replace the ADDRESS arguments with those functions to make it even more flexible.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions