+ Reply to Thread
Results 1 to 6 of 6

month to date calculation based on month selection from list box

  1. #1
    Registered User
    Join Date
    04-06-2016
    Location
    KSA
    MS-Off Ver
    2013
    Posts
    7

    Question month to date calculation based on month selection from list box

    Hello,

    Could you please support to get the formula for Month to date calculation based on month selection from the list box. Currently I have made with SUMIFS formula and it is getting too long due adding each month with if condition. Here is the current formula. is there any way to reduce or replace it this. the sample file has multiple columns selection also.

    IF(K4="jan",SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,$K$4),IF($K$4="feb",SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,"jan")+SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,$K$4),IF($K$4="mar",SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,"jan")+SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,"feb")+SUMIFS($H$2:$H$273,$A$2:$A$273,$K$3,$D$2:$D$273,$J8,$B$2:$B$273,K$7,$C$2:$C$273,$K$4),0))).

    The sample file is attached.

    regards,
    Jamaludheen
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,503

    Re: month to date calculation based on month selection from list box

    you are already using a sumifs
    try on its own
    =SUMIFS(H:H,A:A,K3,C:C,K4)

    Put in K8 And copy to the other cells
    =SUMIFS($H:$H,$B:$B,K$7,$D:$D,$J8,$A:$A,$K$3,$C:$C,$K$4)


    You also have the dates entered as Text, it does not matter in this case , but may do in future manipulation

    Not sure why you have the IF test
    and then SUMIFS using the Month criteria again

    you can modify the SUMIFS() to include the 2015 and 2016 cells and the channel

    =SUMIFS($H:$H,$B:$B,K$7,$D:$D,$J8,$A:$A,$K$3,$C:$C,$K$4)
    Last edited by etaf; 02-06-2017 at 05:15 AM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-06-2016
    Location
    KSA
    MS-Off Ver
    2013
    Posts
    7

    Re: month to date calculation based on month selection from list box

    Hi,

    Thanks for your reply. Your formula is working for the month individual month. But I need to cumulative total. E.g. If I select FEB, then it should calculate JAN+FEB, if MAR, then JAN+FEB+MAR so on...

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,503

    Re: month to date calculation based on month selection from list box

    Oh Ok,
    in which case you do need to change the month to be a "real" month , it can still display just the 3 letters
    Jan, Feb. March etc

    so when you have Sep - do you want Jan-Sept ?

    you can then continue to use the SUMIFS()
    but you can say >= date , <= date

    so >= 1st Jan and <= 31st Mar

    but the data will need to be reformated

    i have to leave now , so hopefully others will reply, otherwise I may get a chance to review this evening UK time

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,248

    Re: month to date calculation based on month selection from list box

    In K8

    =SUMPRODUCT(($H$2:$H$1000)*($A$2:$A$1000=$K$3)*($B$2:$B$1000=K$7)*($D$2:$D$1000=$J8)*(MONTH($C$2:$C$1000 &0)<=MONTH($K$4&0)))


    Copy across and down

    Results in table N:O
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,248

    Re: month to date calculation based on month selection from list box

    See attached: Sheet3 which has Excel dates in Column C
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculation of days per month for dates beginning in one month and ending in another month
    By Fahrettin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2015, 04:52 PM
  2. [SOLVED] Date Calculation need for First of the month following 1 month
    By bwmuhich in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-20-2014, 01:17 PM
  3. Moving YTD Formula Based on drop down list month selection
    By Webbn111 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-28-2014, 02:22 PM
  4. Changing number of days in a month based on month chosen from a dropdown list.
    By st_judeu@yahoo.com in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-08-2013, 02:26 PM
  5. [SOLVED] VBA to identify the current month and previous month based on system date
    By ravikumar00008 in forum Excel General
    Replies: 10
    Last Post: 07-26-2012, 10:04 AM
  6. [SOLVED] Excel month dropdown list to generate day of week and date for selected month
    By aaaaaaaa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-17-2010, 02:45 PM
  7. Replies: 3
    Last Post: 09-25-2007, 10:26 AM

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