Sum a Column

[Steveal]

I have a column of monthly returns covering several years. Each month
I add a new figure to the bottom of the list.
I've learned to reference the new figure using:

=INDIRECT("B" & COUNTA(B1:B50))

But how do I find the SUM of the last 12 months figures?

Thanks,

Steve

 

[Pete_UK]

Suppose your dates are in column A and the numbers to sum in column B.
You can use this approach:

=SUMIF(A:A,"<="&TODAY(),B:B) - SUMIF(A:A,"<"&TODAY()-365,B:B)

Hope this helps.

Pete

 

[Steveal]

Pete,

This would work on daily dates, but my date column shows only months:

Mar-10 50
Apr-10 40
May-10 30
Jun-10 50
Jul-10 60
Aug-10

However, these monthly dates (when I look in the formula window) are
7/1/2010, 8/1/2010 etc.

I've input my gain for July (60) but because we are already beyond
8/1/2010, the calculation has moved on to the August period.

Is there a way to make your formula respond only to Months, or make my
Months ignore the days?


Steve

 

[Steveal]

OK, I can find an answer myself.
Instead of my monthly dates being the first of the month (Seems to be
Excel's default), make them the last day of the month and your formula
works great!

Steve

 

[Pete_UK]

Hi Steve,

glad you found a way to get it to work with your data - thanks for
feeding back.

Pete