2

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?

Improve this question
1
  • 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, 2019 at 23:10

1 Answer 1

Reset to default
3

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))
Improve this answer
2
  • 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, 2019 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, 2019 at 3:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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