1

I am trying to calculate the sum of a range of cells relative to the current cell. This is what I have, and it works:

=SUM(INDIRECT(ADDRESS(ROW()-6,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-5,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-4,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-3,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-2,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-1,COLUMN()-1)), INDIRECT(ADDRESS(ROW(),COLUMN()-1)))

So the sum of the last 7 rows (including current one), column to the left. Is there an easier way to do this?

  • I would need a sample sheet showing sample input, desired output, and current output to see if your solution is overly complicated but I have provided a simplification of your current formula. I could think of a ton of ways to do this in a much simpler fashion but it all depends on the design of your spreadsheet and what you are trying to accomplish. – CodeCamper Nov 17 at 23:10
2

This is as simple as I could get using INDIRECT (RC notation):

=SUM(INDIRECT("R"&ROW()-6&"C"&COLUMN()-1&":R"&ROW()&"C"&COLUMN()-1,))

However using OFFSET is much shorter (B7 being the cell the formula is in)

=sum(offset(B7,-6,-1,7,1))

Just because I put B7 in the above formula doesn't mean it is not relative, it is, if you copy and paste it into any cell it will "relatively" work but if you insist on not having any cell reference then combining the two methods would produce the cleanest way:

=sum(offset(indirect(address(row(),column())),-6,-1,7,1))
  • Thank you, I was just hoping for a solution using relative cells so that I could copy and paste the same formula for many cells. I'm still learning so I was just wondering if i was missing like a loop function or something. The first one works well, thank you! – MCMastery Nov 18 at 3:42
  • @MCMastery the second function is using relative cells as well... once you make B7 the cell you pasted it in, when you copy and paste it will always be relative... I'm not sure I understand what scenario the second function would not be relative I still think you should use the offset unless you can give me an example of where it "won't" be relative. That is the whole point of the offset function is to accomplish exactly what you are trying to do... relative referencing. – CodeCamper Nov 18 at 3:53

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.