# Sum data in column for dates until today

1. ## Sum data in column for dates until today

Hey Guys,

File attached!

I'm working on a worksheet (Sales Report) where row 1 contain all the momths of this year (October in A1, November in B1, etc) and row 2 contains values that I want to summate. (The reason why the first month is October is beacause our fiscal years starts in October 2017 and ends September 2018)

However, I only want to automatically summate the values in column B from january till today. The values in the other cells of column B (corresponding to dates later in the year) should not be summated (yet).

So the summation should change automatically monthly (because in each new month that current month should now be included in the summation).

Does anybody know how I can achieve this?

Thanks a lot!

2. ## Re: Sum data in column for dates until today

welcome to the forum, my fellow Singaporean. 2 ways you can try:
=SUM(A2:INDEX(A2:L2,MATCH(TEXT(TODAY(),"mmmm"),\$A\$1:\$L\$1,0)))

my preferred method would be to change the dates in row 1. type 1oct17 for cell A1. do the same for the rest; 1nov17, 1dec17, etc. select the dates and right-click -> FOrmat Cells -> Custom:
mmmm

you now have accurate dates and presented in the way you want it. then:
=SUMIF(\$A\$1:\$L\$1,"<="&EOMONTH(TODAY(),0),A2:L2)

3. ## Re: Sum data in column for dates until today

Dear benishiryo

Thanks a lot my friend!! Is there any chance you could insert both options in the file I attached?

Anyone? :-)

5. ## Re: Sum data in column for dates until today

Originally Posted by benishiryo
welcome to the forum, my fellow Singaporean. 2 ways you can try:
=SUM(A2:INDEX(A2:L2,MATCH(TEXT(TODAY(),"mmmm"),\$A\$1:\$L\$1,0)))

my preferred method would be to change the dates in row 1. type 1oct17 for cell A1. do the same for the rest; 1nov17, 1dec17, etc. select the dates and right-click -> FOrmat Cells -> Custom:
mmmm

you now have accurate dates and presented in the way you want it. then:
=SUMIF(\$A\$1:\$L\$1,"<="&EOMONTH(TODAY(),0),A2:L2)
Hey Benishiryo,

I managed to change the dates and format the cells so it is presented in the right way. Then, when I try to insert your formula, it doesn't work. Excel tells me that it isn't a formula although it starts with '='.... Please help me, I am stuck.

6. ## Re: Sum data in column for dates until today

i would imagine you would still face the same problem if i upload the file, you copy it, and paste it back into your own. So why not upload the file you have, pasted with the formula you tried and have problem with.

anyway, i have uploaded the file with the 2 solutions. see if it helps you with it.

7. ## Re: Sum data in column for dates until today

In #1 you said from Jan, but in sample worksheet it is from Oct
So you need 2 formulas:
From beginning to current month:
=SUMPRODUCT((DATE(2017,COLUMN(\$J:\$U),1)<=EOMONTH(TODAY(),0))*\$A\$2:\$L\$2)
From Jan to current month:
=SUMPRODUCT((DATE(2017,COLUMN(\$J:\$U),1)>=DATE(2018,1,1))*(DATE(2017,COLUMN(\$J:\$U),1)<=EOMONTH(TODAY(),0))*\$A\$2:\$L\$2)

Thanks guys!

9. ## Re: Sum data in column for dates until today

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1