+ Reply to Thread
Results 1 to 14 of 14

Using TODAY function inside the SUMIFS function

  1. 04-25-2019, 12:51 PM #1
    taraberg_321
    taraberg_321 is offline
    Registered User
    Join Date
    04-25-2019
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft Office Home and Business 2016 Version 1903
    Posts
    6

    Using TODAY function inside the SUMIFS function

    Hello,

    I've just joined in order to ask this question, so thank you all in advance for listening.

    My sum range, criteria range 1, criteria 1 are all good to go. The problem comes in when I try to add another criteria range and criteria in order to narrow the results down to last 6 months. Because I do not want to explain to my boss how to fiddle with dates - I wanted to incorporate the TODAY function into the SUMIFS function. My range is the ship date and my criteria should be last 6 months of dates.

    Is what I am trying impossible? Should I give up and use a helper cell instead? Thanks in advance!excel today capture.PNG
    Reply With QuoteRegister To Reply
  2. 04-25-2019, 12:53 PM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,676

    Re: Using TODAY function inside the SUMIFS function

    Welcome to the forum!

    The TODAY function looks like this:

    =TODAY()

    What are you trying to do with it?
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 04-25-2019, 12:55 PM #3
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Using TODAY function inside the SUMIFS function

    Untested, but try putting the numbers of days to subtract outside of the Today() function.

    Today()-183
    HTH
    Regards, Jeff
    Reply With QuoteRegister To Reply
  4. 04-25-2019, 12:58 PM #4
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,498

    Re: Using TODAY function inside the SUMIFS function

    I think Jeff gave you the answer, I just put this in a test sheet and it worked ok.
    =SUMIFS(A:A,C:C,D1,E:E,F1,B:B,"<"&TODAY()-183)
    with the bold part being in question.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  5. 04-25-2019, 01:01 PM #5
    taraberg_321
    taraberg_321 is offline
    Registered User
    Join Date
    04-25-2019
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft Office Home and Business 2016 Version 1903
    Posts
    6

    Re: Using TODAY function inside the SUMIFS function

    I totally wanted that to work!
    It says =volatile and comes back with a 60 when I know the answer is 2,938.
    Reply With QuoteRegister To Reply
  6. 04-25-2019, 01:02 PM #6
    taraberg_321
    taraberg_321 is offline
    Registered User
    Join Date
    04-25-2019
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft Office Home and Business 2016 Version 1903
    Posts
    6

    Re: Using TODAY function inside the SUMIFS function

    wait! I tried a second time and it does work! thank you very much
    Reply With QuoteRegister To Reply
  7. 04-25-2019, 01:02 PM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,676

    Re: Using TODAY function inside the SUMIFS function

    Will you please attach a sample Excel workbook?

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Reply With QuoteRegister To Reply
  8. 04-25-2019, 01:08 PM #8
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Using TODAY function inside the SUMIFS function

    Quote Originally Posted by taraberg_321 View Post
    wait! I tried a second time and it does work! thank you very much
    Glad to know you have your solution.

    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 With QuoteRegister To Reply
  9. 04-25-2019, 01:08 PM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,676

    Re: Using TODAY function inside the SUMIFS function

    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 With QuoteRegister To Reply
  10. 04-25-2019, 01:45 PM #10
    taraberg_321
    taraberg_321 is offline
    Registered User
    Join Date
    04-25-2019
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft Office Home and Business 2016 Version 1903
    Posts
    6

    Re: Using TODAY function inside the SUMIFS function

    I'm back. Just realizing that my last 12 months and last 6 months brings the exact same totals - even though I know the totals are different when I filter my table. I am unable to upload file attachment. It is xlsx. Checking the size now - perhaps it is too large and I can shave it down for the example.
    Reply With QuoteRegister To Reply
  11. 04-25-2019, 03:34 PM #11
    taraberg_321
    taraberg_321 is offline
    Registered User
    Join Date
    04-25-2019
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft Office Home and Business 2016 Version 1903
    Posts
    6

    Re: Using TODAY function inside the SUMIFS function

    I am attempting to add the example file now. Thank you all for your time.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 04-25-2019, 05:35 PM #12
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Using TODAY function inside the SUMIFS function

    First off, the dates in Sheet1 column J are really not dates. In L2 put >> =ISNUMBER(J2) >> you will get FALSE which means J2 is not a date.

    Leave the formula in L2

    Text to Columns
    • Highlight column J
    • Data >> Data Tools >> Text to Columns >> Finish

    Now L2 should read TRUE

    Now for the data. In Sheet1 column J, 365 days - Today() is 25-Apr-2018. The oldest date in column J is 12-Jun-2108.

    The formula in U2 you are trying to use is >> =SUMIFS(Sheet1!I:I,Sheet1!C:C,A2,Sheet1!J:J,"<"&TODAY()-365)

    Nothing is less than 25-Apr-2018.

    Maybe you can tell us what you are trying to achieve and what you believe the answer is when you filter Sheet1 based on your criteria.
    Last edited by jeffreybrown; 04-25-2019 at 05:37 PM.
    Reply With QuoteRegister To Reply
  13. 04-26-2019, 09:47 AM #13
    taraberg_321
    taraberg_321 is offline
    Registered User
    Join Date
    04-25-2019
    Location
    Charlotte, NC
    MS-Off Ver
    Microsoft Office Home and Business 2016 Version 1903
    Posts
    6

    Re: Using TODAY function inside the SUMIFS function

    Hi Jeffrey,

    My main issue was that I needed to perform the text to columns action that you suggested. That corrected my problem finding the last 6 month quantity sold.

    Now I can pull 12 months from PRM system before dropping into my workbook, then use the calculation to find only 6 months. The 12 month will already be filtered so that criteria is no longer needed - I can just sum all.

    Prior to this I had been pulling two tables of data, Your help is very much appreciated. - Tara
    Reply With QuoteRegister To Reply
  14. 04-26-2019, 10:32 AM #14
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Using TODAY function inside the SUMIFS function

    Hi Tara,

    Good to know you are getting further along with your data.

    You are very welcome. Thanks for the feedback and the rep.
    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. INDIRECT/ADDRESS inside LINEST function versus inside INTERCEPT
    By slny06 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2019, 08:51 AM
  2. Calling function inside function. (aka nested function)
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2015, 05:58 AM
  3. Right function inside a Match function - highlight based on criteria
    By Brooklyn23 in forum Excel General
    Replies: 11
    Last Post: 06-01-2015, 02:21 AM
  4. Help nesting an OR function inside a SUMIFS function
    By wes228 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-21-2014, 06:22 PM
  5. Trying to use TODAY function in SUMIFS formula
    By mike703 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2013, 03:22 PM
  6. match function inside offset function to autofill a formula to start at end of prev series
    By mbkr29 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-19-2013, 01:35 PM
  7. populate a cell once & using TODAY() inside function.
    By dblword in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-23-2009, 02:44 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