How do I sum the last 10 rows of column "D". with the result in a cell in column "F&q-CodePudding

I have been using this and it works, but everyday I add a new row and have to insert a row above this formula, that's why I want it to display in column "F" somewhere:

=SUM(INDIRECT(ADDRESS(ROW()-10,COLUMN(),4) &":"& ADDRESS(ROW()-1,COLUMN(),4)))

CodePudding user response：

You can try SUMPRODUCT:

=SUMPRODUCT((ROW(A:A)<=COUNTA(A:A))*(ROW(A:A)>COUNTA(A:A)-10)*A:A)

There can't be blanks in A:A

CodePudding user response：

If your range of values in D:D is contiguous (i.e., no blanks) and begins at the top of the column (with or without a header), you can use this:

=ArrayFormula(SUMIF(ROW(D:D),">"&COUNTA(D:D)-10,D:D))

If there are interspersed blanks in the range, you can use either of these:

=SUM(SORTN(FILTER(D:D,ISNUMBER(D:D)),10,0,FILTER(ROW(D:D),ISNUMBER(D:D)),0))

=SUM(QUERY(FILTER({D:D,ROW(D:D)},ISNUMBER(D:D)),"Select Col1 ORDER BY Col2 DESC LIMIT 10"))