+ Reply to Thread
Results 1 to 6 of 6

SUMIFS formula not working with Dates

  1. #1
    Registered User
    Join Date
    02-20-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    32

    SUMIFS formula not working with Dates

    I think I can use the SUMIFS formulato add qty's based on whether the transaction date falls within a specified date range.

    I have a workbook with 2 sheets; 1st sheet has the qty's and the transaction dates and the 2nd sheet has the criteria.

    Qty's in Sheet 1 are in Column H
    Dates in Sheet 1 are in Column D

    So in sheet 2 i need a formula in each row to sum the qty's in sheet 1 column H as long as the date in sheet 1 column D falls within the dates in sheet 2 column C and sheet 2 column U.

    My formula reads: =SUMIFS(Sheet1!H:H, Sheet1!D:D, ">="&Sheet2!C2, Sheet1!D:D,"<="&Sheet2!U2)

    From there I would expect to be able to copy the formula down so that this calculation is performed for each row in sheet 2 (several date ranges). Any ideas why this isn't working?

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIFS formula not working with Dates

    Your formula looks OK to me, what result do you get, an error or the wrong value?
    Audere est facere

  3. #3
    Registered User
    Join Date
    02-20-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: SUMIFS formula not working with Dates

    I just get zeros. I've gone through the data and have confirmed there should be qty's summed as there are transaction dates that do fall between the date range on sheet 2

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFS formula not working with Dates

    Either some or all of your dates are not really dates, but text strings that look like dates.
    Or the values you are summing aren't really numbers, but "numbers stored as text"

    Can you post a sample book?

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIFS formula not working with Dates

    Make sure that your quantity column contains numbers, not text formatted numbers - what do you get with

    =COUNT(Sheet1!H:H)

    That should count the numbers in that column - if you get zero with that formula then it's text formatted - try the same with the date columns - they should be numbers too

  6. #6
    Registered User
    Join Date
    02-20-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: SUMIFS formula not working with Dates

    yeah it looks to be a problem with the date formatting in Sheet 1 column D.

    Thanks to all for your help

+ 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 between Dates not working
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2013, 05:47 AM
  2. [SOLVED] SUMIFS formula not working in VBA
    By meechie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-09-2012, 03:06 PM
  3. Excel 2007 : SUMIFS Formula Not Working
    By milliemoo in forum Excel General
    Replies: 7
    Last Post: 03-06-2012, 12:16 PM
  4. sumifs with dates not working
    By paualou76 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-30-2011, 08:33 PM
  5. Replies: 6
    Last Post: 09-21-2009, 02:26 PM

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