sum last nth cells

[Cliff Partridge]

I want to be able to sum a range of nth cells (not every nth cell).

For example, I have monthly data in a column and I want the sum to be
YTD. So in February the formula needs to sum the last two occupied
cells in the column. I can use the OFFSET formula to grab the last
cell, but I can't figure out how to sum the last nth cells.

Cliff

 

[Frank Kabel]

Hi
try
=SUM(OFFSET($A$1,COUNTA($A:$A)-1,0,-2)

 

[Cliff Partridge]

The formula from Frank works well and I have it linked to a number
that represents the number of months to go back for YTD (I changed the
-2 with A1 which is 1 for Jan, 2 for Feb etc.).

My question now is, how can I set this up so that when I change the
number in A1 the formula will return YTD based on that number
regardless of how many months in 2004 I have entered.

Currently my column of data starts with Jan 2003 in row 2 and then
goes "down" to the current month in 2004. So, if I was to enter 4 in
A1, I want the formula to return YTD 2004 April even if I've entered
2004 through June. But if I change A1 to 6, then I want YTD June.

Cliff

 

[Frank Kabel]

Hi Cliff
if your dates are stored in column A (starting in row 2) and your
values in column B try the following (we will use tow helper cells)
In B1 enter the array formula (entered with CTRL+SHIFT+ENTER)
=MATCH(1,--(YEAR(A2:A100)=2004),0)

in C1 enter the array formula
=MATCH(1,(MONTH(A2:A100)=A1+1)*(YEAR(A2:A100)=2004),0)

To get the YTD sum enter the following formula
=SUM(OFFSET($B$1,B1,0,C1-B1))

Works for the months 1-11 (not for december)

 

[2rrs]

I want to be able to sum a range of nth cells (not every nth cell).

For example, I have monthly data in a column and I want the sum to be
YTD. So in February the formula needs to sum the last two occupied
cells in the column. I can use the OFFSET formula to grab the last
cell, but I can't figure out how to sum the last nth cells.

Cliff

Give this a try.

If your dates are true dates in Col A; and values in Col B
You could use:
=SUMIF(A2:A100,"<="&TODAY(),B2:B100)