to-date total based on current date
 

am trying to create a forumula that will give a cumulative or
"to-date" total that will exclude future months from the total.

i.e. ithe point in time is august and I need to show a to-date number
through august only even though september and october are included in
the spreadsheet. Next month will need to show through september and
exclude october.


columns: july august september october year-to-date
rows : 100 200 300 400
year-to-date as of august should show 300 if the point in time is
august and show 600 if it's september


is there a way to write a formula using time or that will change based
on the current date?


thanks for your help.
Tina

to-date total based on current date
 

Hi,

Assume
A1 is July date B1 is July Value
A1 is Aug date B2 is Aug Value

etc

=SUMPRODUCT(--(--(MONTH(A1:A4))<=(MONTH(TODAY()))),--(--(B1:B4)))

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=569936

to-date total based on current date
 

I didn't ge this to work. I put in ten months- 1-10 in columns A1:A10
and it still added all ten months vs giving me through august only.

any suggestions?
Thanks for trying!
Tina
VBA Noob wrote:
Hi,

Assume
A1 is July date B1 is July Value
A1 is Aug date B2 is Aug Value

etc

=SUMPRODUCT(--(--(MONTH(A1:A4))<=(MONTH(TODAY()))),--(--(B1:B4)))

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=569936

to-date total based on current date
 

Try

=SUMPRODUCT(--(A1:A13<=DATE(YEAR(TODAY()),MONTH(TODAY()),CHOOSE( MONTH(TODAY()),31,28,31,30,31,30,31,31,30,31,30,31 )))*(--(B1:B13)))

May need to change the 28 for leap years

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=569936