'How do I sum the last 10 rows of column "D". with the result in a cell in column "F"?
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)))
Solution 1:[1]
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
Solution 2:[2]
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"))
Solution 3:[3]
use:
=SUM(QUERY(SORT(D2:D; ROW(D2:D); );
"where Col1 is not null limit 10"; ))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | basic |
| Solution 2 | Erik Tyler |
| Solution 3 | player0 |