This is a bit tricky:

SUM(INDIRECT("P9:"&IF(MONTH(NOW())=1,"AA9",CHAR(78+MONTH(NOW()))&"9")))

CHAR(78+MONTH(NOW())) turns the month into a

letter; 1->P, 2->Q, etc.

If you move these cells inthe worksheet, then

you'll need to modify the formula.

The IF is needed to make sure that in January

you get everything from P9 to AA9.

Hope this helps!

Thanks, but I need to copy the formula both down the column, as well as to other worksheets. As it is, it would be a long process changing the formulas in each cell to accomodate the cells in the totals. "Hard Coding" is something I don't care to really get involved in. Takes too much time.

Try this:

=SUM(OFFSET(P9,,,,MOD(MONTH(NOW())-2,12)+1))

where P9 represents the January cell.

Right you are. I sat in my thinking chair

and came up with:

[with the data in P9 to AA9:-]

IF(MONTH(NOW())=1,SUM(P9:AA9),SUM(OFFSET(P9,0,0,1,MONTH(NOW())-1)))

This doesn't require any hard-coding whatsoever.

[OFFSET(P9,A,B,C,D) generates a range starting at

P9, offset by A rows and B columns, that is

C rows high and D columns wide.]

I originally feared that you might have to use

something like:

IF(MONTH(NOW())>=2,P9)+IF(MONTH(NOW())>=3,Q9)+IF(MONTH(NOW())>=4,R9)+...+IF(MONTH(NOW())=12,Z9)+IF(MONTH(NOW())=1,SUM(P9:AA9))

This still works but is a bit unwieldy.

That worked fine! thanks

Can you explain the formula to me?

The OFFSET provides a dynamic range width to sum, driven by the current month, starting with P9 as the leftmost cell. If it's Month X, you want to sum the leftmost X-1 cells. January is an exception to this because when X = 1, X - 1 returns 0 (no cells summed), therefore the MOD function translates that 0 into a 12, and 12 cells will be summed. The MOD function is not intuitive; suffice to say it's what accommodates your one-month lag.

Thanks for the explanation.......

I thought I was pretty savy on Excel....

Just shows you can never think you know it all.

Great Job!