a monthly running total

C

carol

I have a worksheet with the months running along the top,
under each month is a value.

Currently i have a running total thats works by summin up
all the months, but this only works because i never enter
values ahead of their time eg i can only enter values up
until the month that it currently is.

If i want to be able to enter future values, i.e. for june
or july 2004 - BUT FOR THEM NOT TO AFFECT THE RUNNING
TOTAL UNTIL THAT TIME - how would i go about achieving
this?
Regards
carol
 
D

Don Guillett

If your column headers are a valid date then you could use this idea
=IF(MONTH(H1)<MONTH(TODAY()),1,2)
 
J

jeff

I found this to work (dates (like 01/01/04 in Row 1, maybe
formatted to 'mmm', and amts to be summed in Row 2):

=SUMPRODUCT(--(A2:G2),--((MONTH(A1:G1)<=MONTH(TODAY()))))

jeff
 
D

Domenic

I have a worksheet with the months running along the top,
under each month is a value.

Currently i have a running total thats works by summin up
all the months, but this only works because i never enter
values ahead of their time eg i can only enter values up
until the month that it currently is.

If i want to be able to enter future values, i.e. for june
or july 2004 - BUT FOR THEM NOT TO AFFECT THE RUNNING
TOTAL UNTIL THAT TIME - how would i go about achieving
this?
Regards
carol
Click to expand...
If I understood you correctly, assuming that your dates are store in cells
A1 through L1 and your values in row 2, put this formula in M2:

=SUM(IF(MONTH($A$1:$L$1)<=MONTH(TODAY()),A2:L2)), to be entered using
ctrl+shift+enter

Make sure that the dates are true dates. Since you don't need days, you can
enter the dates this way: March 2004 and format it the way you want.

If you have more than one row of data, you can also copy the formula down
the column.

Hope this helps!
 
Top