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 Noo

 

[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]

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