How do I refer to the cell a formula is written in in Google Sheets?

Question

I'm trying to write a formula to add the contents of a column in cell D5, so normally I would write =SUM(D2:D4). However, the location of the formula cell (currently D5) will change as I add more rows. How do I write a formula that accounts for this?

(The formula I want would hypothetically look something like =SUM(D2:CellAboveCurrentCell).)

Thank you!

score 2 · Accepted Answer · answered Sep 07 '20 at 21:57

2

Try this formula:
=Sum(D2:INDIRECT(ADDRESS(ROW()-1,COLUMN())))

See this post for a more detailed explanation.

answered Sep 07 '20 at 21:57

mcernak

9,050
1
5
13

score 1 · Answer 2 · answered Sep 07 '20 at 22:02

1

You can use INDEX function:

=SUM(D2:INDEX(D:D;ROW()-1;1))

answered Sep 07 '20 at 22:02

basic

11,673
2
9
26

How do I refer to the cell a formula is written in in Google Sheets?

2 Answers2