+ Reply to Thread
Results 1 to 17 of 17

SUMIFS Help - Multiple date ranges

  1. #1
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Question SUMIFS Help - Multiple date ranges

    I'm trying to count some numbers if they are between multiple ranges of #s.

    If in this example, count the #s that fall between the start and end dates, if they also fall in a calendar month a3 = 1/1/15, A4 = 2/1/15, A5 = 3/1/15, A6 = 4/1/15 and so on.

    Start date = 11/15/14
    End Date = 3/31/14
    Count_Stuff = For the purposes of this example it's 72 things that are happening between the start and end dates above.

    It seems to work until I hit the end date of 3/31/15.

    =SUM(SUMIFS(Count_Stuff,Start_Date,"<="&A3,End_Date,">="&A4)) -- Then copy/drag down

    Results:
    1/1/2015 72
    2/1/2015 72
    3/1/2015 0
    4/1/2015 0
    5/1/2015 0

    The results for 3/1 - 4/1 should also be 72 since the end date is between those ranges, but it's not working.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,177

    Re: SUMIFS Help - Multiple date ranges

    I think it would be eaiser to help (and to understand) if you attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: SUMIFS Help - Multiple date ranges

    Book1.xlsxHere you go...

  4. #4
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: SUMIFS Help - Multiple date ranges

    hi dspblues ,

    the data provided is not enough to explain your requirement ,

    All the start date is 15-11-2014 & 31-03-2015
    Your require date 01-01-2015 , or please explain it in more details

    Punnam

  5. #5
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: SUMIFS Help - Multiple date ranges

    Hi,

    You can use the formula in B24 and copy down, this will bring 72 for 3/1 but how are you saying that 4/1 should also get this as End date is 3/31/15.

    =SUMPRODUCT((Start_Date<=A24)*(End_Date>=A24)*Count_Stuff)

  6. #6
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: SUMIFS Help - Multiple date ranges

    4/1 shouldn't get it.

    This what I'm trying to do:

    I'm going to have multiple monthly projects ruining that are 4-6 months in duration. On any given month, I need to count how many widgets are in flight.

  7. #7
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: SUMIFS Help - Multiple date ranges

    Quote Originally Posted by misrasomendra View Post
    Hi,

    You can use the formula in B24 and copy down, this will bring 72 for 3/1 but how are you saying that 4/1 should also get this as End date is 3/31/15.

    =SUMPRODUCT((Start_Date<=A24)*(End_Date>=A24)*Count_Stuff)
    This seems to work! Can you explain why? I'm not that familiar with sumproduct. Based on the description of what it does... it doesn't seem like it would. I also would have expected the end date to have to be >= 2/1/15.

  8. #8
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: SUMIFS Help - Multiple date ranges

    Quote Originally Posted by dspblues View Post
    This seems to work! Can you explain why? I'm not that familiar with sumproduct. Based on the description of what it does... it doesn't seem like it would. I also would have expected the end date to have to be >= 2/1/15.
    I did not understood "I also would have expected the end date to have to be >= 2/1/15" ????

    You can modify your formula with the one below:

    =SUMIFS(Count_Stuff,Start_Date,"<="&A24,End_Date,">="&A24)

  9. #9
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: SUMIFS Help - Multiple date ranges

    Can you explain how the formula works?

  10. #10
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: SUMIFS Help - Multiple date ranges

    First Remove SUM than see the reference changes I made to formula.

  11. #11
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: SUMIFS Help - Multiple date ranges

    I think the SUMIFS formula works... I thought I'd have to look at the end date if it was >= 2/1/15, but I guess not.

  12. #12
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: SUMIFS Help - Multiple date ranges

    I am not getting what are you looking for? Both my formula and your revised formula deliver the same results as you mentioned in your first post.

  13. #13
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: SUMIFS Help - Multiple date ranges

    I never said it didn't work. I asked you to please explain it.

  14. #14
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: SUMIFS Help - Multiple date ranges

    I think I tried that in my comment#10.

  15. #15
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: SUMIFS Help - Multiple date ranges

    I see my error, thanks for the help. I was close!

  16. #16
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: SUMIFS Help - Multiple date ranges

    Can you mark the thread as SOLVED now.

  17. #17
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: SUMIFS Help - Multiple date ranges

    I did, yesterday.

+ 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 or SUMPRODUCT with multiple criteria including date ranges
    By baxcat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2013, 09:40 AM
  2. [SOLVED] SUMIFS with time and date name ranges
    By tradersumit in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2012, 07:47 AM
  3. Excel 2007 : Using SUMIFS and date ranges...
    By anthropaulogy in forum Excel General
    Replies: 3
    Last Post: 09-06-2012, 07:05 AM
  4. Sumifs Using Date Ranges
    By Loisw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-21-2012, 07:43 AM
  5. How to use SUMIFS when using two different date ranges
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-20-2011, 07:30 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