+ Reply to Thread
Results 1 to 8 of 8

Condition Formula between dates then sum if meets between those dates

  1. 06-30-2017, 01:22 PM #1
    rhett7660
    rhett7660 is offline
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Condition Formula between dates then sum if meets between those dates

    Hello,

    I have a column with dates in it. I also have another column with dollar amounts in it. I would like to have a formula look at the dates column and if the date falls within the next two years from todays date, add the money up.

    Column P12:P14 = Dates Column
    Column J12:J14 = Dollar amounts

    Show me total for items that fall withing the next two years
    Show me total for items that fall within the next five years

    I have tried the following (but I am getting errors):

    =SumIF(CFMB!P$12:P14,"<="&TODAY()+728,CFMB!P$12:P14,"<>"&TODAY()+364,CFMB!P$12:P14,">"&TODAY()(SUM(CFMB!J$12:J14))


    Thanks
    Last edited by rhett7660; 06-30-2017 at 01:44 PM. Reason: Solved
    Reply With QuoteRegister To Reply
  2. 06-30-2017, 01:29 PM #2
    rhett7660
    rhett7660 is offline
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Re: Condition Formula between dates then sum if meets between those dates

    I have also used the following but it isn't summing the dollar amounts.

    =SUMIFS(CFMB!K$12:K14, CFMB!J$12:J14,"<="&TODAY()+728, CFMB!J$12:J14,"<>"&TODAY()+365, CFMB!J$12:J14,">"&TODAY())
    Last edited by rhett7660; 06-30-2017 at 01:36 PM.
    Reply With QuoteRegister To Reply
  3. 06-30-2017, 01:31 PM #3
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Condition Formula between dates then sum if meets between those dates

    Something like...
    For next 2 years.
    =SUMIFS(CFMB!J$12:J14,CFMB!P$12:P14,"<="&DATE(YEAR(TODAY())+2,MONTH(TODAY()),DAY(TODAY())),CFMB!P$12:P14,">"&TODAY())

    Just change +2 to +5 for 5 years.

    Edit: Hmm, if it's in range of Today()+ 1 Year to Today()+ 3 years...
    =SUMIFS(CFMB!J$12:J14,CFMB!P$12:P14,"<="&DATE(YEAR(TODAY())+3,MONTH(TODAY()),DAY(TODAY())),CFMB!P$12:P14,">="&DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY())))
    Last edited by CK76; 06-30-2017 at 01:33 PM.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein
    Reply With QuoteRegister To Reply
  4. 06-30-2017, 01:34 PM #4
    rhett7660
    rhett7660 is offline
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Re: Condition Formula between dates then sum if meets between those dates

    CK76,

    Trying this out now!
    Reply With QuoteRegister To Reply
  5. 06-30-2017, 01:44 PM #5
    rhett7660
    rhett7660 is offline
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Re: Condition Formula between dates then sum if meets between those dates

    Quote Originally Posted by CK76 View Post
    Something like...
    For next 2 years.
    =SUMIFS(CFMB!J$12:J14,CFMB!P$12:P14,"<="&DATE(YEAR(TODAY())+2,MONTH(TODAY()),DAY(TODAY())),CFMB!P$12:P14,">"&TODAY())

    Just change +2 to +5 for 5 years.

    Edit: Hmm, if it's in range of Today()+ 1 Year to Today()+ 3 years...
    =SUMIFS(CFMB!J$12:J14,CFMB!P$12:P14,"<="&DATE(YEAR(TODAY())+3,MONTH(TODAY()),DAY(TODAY())),CFMB!P$12:P14,">="&DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY())))
    CK76 the second one worked perfectly! Thank you very much.

    I also liked how you did the date vs &TODAY()+365.

    Thank you again
    Reply With QuoteRegister To Reply
  6. 06-30-2017, 01:54 PM #6
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Condition Formula between dates then sum if meets between those dates

    You are welcome and thanks for the rep
    Reply With QuoteRegister To Reply
  7. 06-30-2017, 02:31 PM #7
    rhett7660
    rhett7660 is offline
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Southern Cali
    MS-Off Ver
    2013
    Posts
    104

    Re: Condition Formula between dates then sum if meets between those dates

    Quote Originally Posted by CK76 View Post
    You are welcome and thanks for the rep
    One more question:

    How would I have it set to do the following:

    Within the next following years:

    Year 1 show me only what is needed for year 1
    Year 2 show me only what is needed for year 2
    Year 3 show me only what is needed for year 3
    etc

    Right now it is keeping a running tally as soon as it hits that year.

    Year 3 should be different from Year 2 since the item in question is only supposed to be purchased in year 3, not year 2 and 3. Does that make sense?
    Reply With QuoteRegister To Reply
  8. 06-30-2017, 03:52 PM #8
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Condition Formula between dates then sum if meets between those dates

    Not sure what you mean. I'm stepping out for the night and am out for the weekend.

    I'll try to look at it next week, but I'd suggest you start new thread referencing this thread to get faster response.

    Regards,
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Dates w/in 48 hours of each other formula/condition
    By JRose0303 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-05-2016, 01:28 PM
  2. Excel IF formula with two dates as condition
    By BdeWinnaar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2014, 08:41 AM
  3. [SOLVED] Countif (or maybe it's Sumproduct?) meets two criteria and between two dates
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-14-2014, 12:50 PM
  4. Check dates in range either same dates or different dates by formula
    By breadwinner in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-12-2013, 07:42 AM
  5. [SOLVED] Using COUNTIFS between dates when one more condition - can they be in one formula or not?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2012, 01:32 PM
  6. Condition formating on due dates between a row of dates
    By irismarcial in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-11-2012, 04:18 PM
  7. Average of dates and times if it meets a specific criteria
    By Shadoweski in forum Excel General
    Replies: 1
    Last Post: 09-28-2010, 10:26 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1