+ Reply to Thread
Results 1 to 15 of 15

Sumifs Formula with dates that change

Hybrid View

tonym33 Sumifs Formula with dates... 05-15-2019, 03:58 PM
Melchizedek Re: Sumifs Formula with dates... 05-15-2019, 05:12 PM
tonym33 Re: Sumifs Formula with dates... 05-15-2019, 05:34 PM
TMS Re: Sumifs Formula with dates... 05-15-2019, 05:29 PM
TMS Re: Sumifs Formula with dates... 05-15-2019, 05:36 PM
tonym33 Re: Sumifs Formula with dates... 05-15-2019, 05:52 PM
TMS Re: Sumifs Formula with dates... 05-15-2019, 06:00 PM
Melvosh Re: Sumifs Formula with dates... 05-15-2019, 06:12 PM
tonym33 Re: Solved Sumifs Formula... 05-17-2019, 12:15 PM
Melvosh Re: Solved Sumifs Formula... 05-17-2019, 12:25 PM
tonym33 Re: Solved Sumifs Formula... 05-17-2019, 02:38 PM
AliGW Re: Solved Sumifs Formula... 05-17-2019, 12:28 PM
Melvosh Re: Sumifs Formula with dates... 05-17-2019, 02:45 PM
tonym33 Re: Sumifs Formula with dates... 05-17-2019, 04:01 PM
Melvosh Re: Sumifs Formula with dates... 05-17-2019, 04:26 PM
  1. #1
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Sumifs Formula with dates that change

    I have a sumifs formula with a date range that is working well. But I would like the formula to change based on the date. So if it's January I want it to pull January 1 thru January 31 and if it's March, March 1 thru March 31st. I have a table that has the dates in it, so is there a way that I can refer to that table and have it do the formula based on that table????

    This is an income statement. I can make the table work for YTD, but when I want only the month I have to do a range. What I can't figure out is how to make the range dynamic to change with the different dates without having to manually do this.

    Thanks
    Last edited by AliGW; 05-17-2019 at 12:28 PM.

  2. #2
    Registered User
    Join Date
    05-04-2019
    Location
    London, England
    MS-Off Ver
    2018
    Posts
    20

    Re: Sumifs Formula with dates that change

    Would you be able to attach a sample?

  3. #3
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Sumifs Formula with dates that change

    Look at the formula as I can get it to work with a very long IF statement, I could figure it out otherwise.
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,019

    Re: Sumifs Formula with dates that change

    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,019

    Re: Sumifs Formula with dates that change

    I can't look at the formula right now, I’m on an iPad.

  6. #6
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Sumifs Formula with dates that change

    The problem is that I don't know how to make a formula that will sum a total based on a date that changes....so if you only want to sum the current month and need the formula to range from the 1st of the month to the end of the month, I can do that. But how do you make the formula so that you can change the month from January to March and the formula will only pull the data for that current month?

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,019

    Re: Sumifs Formula with dates that change

    For a date in cell A1, the first day is
    Formula: copy to clipboard
    =DATE(YEAR(A1), MONTH(A1), 1)
    . The last day would be
    Formula: copy to clipboard
    =EOMONTH(A1, 0)


    So they would be your start and end criteria for your SUMIFS, COUNTIFS, etc.

  8. #8
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sumifs Formula with dates that change

    Try this in cell C7:
    =SUMIFS(Table_Query_from_Timberline_Data_Source[Debit],Table_Query_from_Timberline_Data_Source[Accounting_Date],">=" & EOMONTH($A$3,-1)+1,Table_Query_from_Timberline_Data_Source[Accounting_Date],"<=" & EOMONTH($A$3,0))+SUMIFS(Table_Query_from_Timberline_Data_Source[Credit],Table_Query_from_Timberline_Data_Source[Accounting_Date],">=" & EOMONTH($A$3,-1)+1,Table_Query_from_Timberline_Data_Source[Accounting_Date],"<=" & EOMONTH($A$3,0))
    Please note, cell A3 must be formatted as an actual date. It can be any date within the month. There are two SUMIFS formulas because apparently SUMIFS can't sum more than single column
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  9. #9
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Solved Sumifs Formula with dates that change

    Melvosh, that works for the date.....how do I include the account too? To me the issue is that based on the date, the column changes and then on the YTD I need to sum multiple columns. For instance EOM is 1/31/2019 look at column 1, but if it's EOM 2/28/2019 then I need column2 and YTD same thing if it's EOM 2/28/2019 I need to sum Column 1&2 by account.

  10. #10
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Solved Sumifs Formula with dates that change

    You would add a criteria range and criteria to each SUMIFS formula. I'm not sure where account is in your workbook from post #4, but if you wanted to use DeptBase, it would look like this:
    =SUMIFS(Table_Query_from_Timberline_Data_Source[Debit],Table_Query_from_Timberline_Data_Source[Accounting_Date],">=" & EOMONTH($A$3,-1)+1,Table_Query_from_Timberline_Data_Source[Accounting_Date],"<=" & EOMONTH($A$3,0),Table_Query_from_Timberline_Data_Source[DeptBase],$B7)+SUMIFS(Table_Query_from_Timberline_Data_Source[Credit],Table_Query_from_Timberline_Data_Source[Accounting_Date],">=" & EOMONTH($A$3,-1)+1,Table_Query_from_Timberline_Data_Source[Accounting_Date],"<=" & EOMONTH($A$3,0),Table_Query_from_Timberline_Data_Source[DeptBase],$B7)

  11. #11
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Solved Sumifs Formula with dates that change

    I have added another attachment. This is the problem. I can get a sumifs to work when the data, including date, are all in one column. This issue is that I don't know how to create a formula that says if it's this date look at this column and if it's this date look at that column. In the file I uploaded, if the date in C2 shows 2/28/2019 then I want it to match or vlookup the account number (B3) and retrieve the value from Column M, if it's 3/31/2019 then I want it to come from column N, etc.
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,259

    Re: Solved Sumifs Formula with dates that change

    Tonymm33 - please note that Ito mark a thread as solved, you need to select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  13. #13
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sumifs Formula with dates that change

    This data is structured differently than the previous workbook. I would use a different formula for this. In C3 and drag down:
    =VLOOKUP($B3,$L$3:$O$5,MATCH(TEXT($C$2,"mmm") & "*",$L$2:$O$2,0),FALSE)
    Does that give you the results you're looking for?

  14. #14
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Sumifs Formula with dates that change

    It is for the month, how would you structure the YTD so that March would sum columns M thru O???

  15. #15
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sumifs Formula with dates that change

    So for the sake of consistency, use this for a single month:
    =SUMPRODUCT((MONTH(DATEVALUE((LEFT($M$2:$O$2,3)&"1")))=MONTH($C$2))*($L$3:$L$5=$B3)*$M$3:$O$5)
    Use this for YTD:
    =SUMPRODUCT((MONTH(DATEVALUE((LEFT($M$2:$O$2,3)&"1")))<=MONTH($C$2))*($L$3:$L$5=$B3)*$M$3:$O$5)
    See if those get you the results you're looking for.

+ 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. Sumifs formula with ingnoring the duplicates dates or shifts or so on
    By Naveenaggarwal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2018, 03:20 AM
  2. Replies: 5
    Last Post: 03-30-2016, 06:01 AM
  3. [SOLVED] Formula to change dates
    By rizmomin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-13-2015, 12:33 PM
  4. sumifs formula for checking dates...
    By captnken in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-01-2014, 02:08 PM
  5. [SOLVED] SUMIFS formula not working with Dates
    By Bigred25 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-11-2014, 10:25 AM
  6. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  7. [SOLVED] Using dates in SUMIFS formula
    By b.p.m in forum Excel General
    Replies: 2
    Last Post: 07-11-2012, 09:11 AM

Tags for this Thread

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