to-date total based on current date

[dreamkeeper]

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

[VBA Noob]

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

[dreamkeeper]

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

[VBA Noob]

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