+ Reply to Thread
Results 1 to 9 of 9

Sum data in column for dates until today

  1. #1
    Registered User
    Join Date
    03-05-2018
    Location
    Singapore
    MS-Off Ver
    Windows 10
    Posts
    44

    Question Sum data in column for dates until today

    Hey Guys,

    PLEASE, PLEASE, PLEASE HELP

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2007, 2010
    Posts
    5,002

    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)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    03-05-2018
    Location
    Singapore
    MS-Off Ver
    Windows 10
    Posts
    44

    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?

  4. #4
    Registered User
    Join Date
    03-05-2018
    Location
    Singapore
    MS-Off Ver
    Windows 10
    Posts
    44

    Re: Sum data in column for dates until today

    Anyone? :-)
    Last edited by ChristianJ; 03-05-2018 at 11:28 PM.

  5. #5
    Registered User
    Join Date
    03-05-2018
    Location
    Singapore
    MS-Off Ver
    Windows 10
    Posts
    44

    Re: Sum data in column for dates until today

    Quote Originally Posted by benishiryo View Post
    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. #6
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2007, 2010
    Posts
    5,002

    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.
    Attached Files Attached Files

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,746

    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)
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-05-2018
    Location
    Singapore
    MS-Off Ver
    Windows 10
    Posts
    44

    Re: Sum data in column for dates until today

    Thanks guys!

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: Sum data in column for dates until today

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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