+ Reply to Thread
Results 1 to 8 of 8

Date range formula showing with VALUE or 0 result

  1. #1
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    12

    Date range formula showing with VALUE or 0 result

    I am consistently getting a VALUE error or 0 back for the formula below

    =SUMIFS(I2:I9999,G2:G9999,">"&A395,G2:G9999,"<w"&B395)

    Column I is value (cells format = number)
    Column G is Date (cell format is long date range e.g. 2013-01-01)
    Cell A395 is start date (cell format is long date range e.g. 2013-01-01)
    Cell B395 is end date (cell format is long date range e.g. 2013-01-31)

    I have copied and paste the values into a new sheet re-formatted the above columns and still not working.
    Any help greatly appreciated.
    Attached Files Attached Files
    Last edited by Aisling; 10-15-2019 at 11:26 AM. Reason: Add attachment

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Date range formula showing with VALUE or 0 result

    The only thing that is sticking out to me is this part:

    =SUMIFS(I2:I9999,G2:G9999,">"&A395,G2:G9999,"<w"&B395)

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Date range formula showing with VALUE or 0 result

    Thank you, I have added a sample of the data set.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Date range formula showing with VALUE or 0 result

    Deleting the w in your formula from post #1 (and putting the dates into cells A395 and B395), gives me -420.89

    See attachment.

    That being said, if you're looking for the total for the month of January, you either want to use "<=" the last day of the month or "<" the first day of the next month.
    Attached Files Attached Files

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Date range formula showing with VALUE or 0 result

    I just noticed that most of the dates are not being recognized as actual dates.

    To fix this, highlight column G > Data > Text to Columns > Finish

  6. #6
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Date range formula showing with VALUE or 0 result

    I have copied and pasted the formula directly off the workbook you attached into the one I sent and it is still showing 0. Would it be something to do with my excel or the way the dates are formatted?
    Attachment 645396

  7. #7
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Date range formula showing with VALUE or 0 result

    Fantastic, you are an absolute star!!
    It's working perfectly now.
    Thank you

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Date range formula showing with VALUE or 0 result

    You're welcome. Happy to help.

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

    Edit: Thanks for the rep!
    Last edited by 63falcondude; 10-15-2019 at 12:09 PM.

+ 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. Cell with Formula Not Showing Date - Only Showing Serial Number
    By jbb89777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-07-2016, 04:33 AM
  2. Slicer for table with date column showing wrong result
    By p3rlend in forum Excel General
    Replies: 1
    Last Post: 04-24-2016, 06:37 AM
  3. Formula Result not showing
    By cemont in forum Excel General
    Replies: 8
    Last Post: 03-02-2012, 12:29 PM
  4. Subtotal with Named Range, result not showing
    By matt4003 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-07-2010, 01:13 PM
  5. Macro Showing Formula and Result
    By Simon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2006, 04:25 AM
  6. [SOLVED] formula in cell not showing result
    By Leolin in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-18-2005, 02:06 PM
  7. Formula result not showing up
    By DrewM in forum Excel General
    Replies: 2
    Last Post: 01-12-2005, 11:19 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