Sum a Column

  • Thread starter Thread starter Steveal
  • Start date Start date
S

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
 
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
 
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
 
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
 
Hi Steve,

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

Pete
 
Back
Top