+ Reply to Thread
Results 1 to 3 of 3

Count Overlapping dates in multiple date ranges with a criteria

  1. 12-02-2016, 03:49 PM #1
    jenn.murphy
    jenn.murphy is offline
    Registered User
    Join Date
    12-02-2016
    Location
    Fort Collins, CO
    MS-Off Ver
    2013
    Posts
    2

    Count Overlapping dates in multiple date ranges with a criteria

    Hello,

    I've been struggling with this formula for a couple of days now.

    I've attached the spreadsheet that I'm working on. It contains locations with start and end dates. What I'm looking for is to find the max number of instances that date ranges overlap with other date ranges for each individual location. You'll notice that I've included a MMULT formula in cells J3:AJ3 that gives me the correct max overlaps for ALL locations, but I need to break it down further by location. I need to know the max instances of date range overlap by location and by date and/or week. Any help is much appreciated as my head is spinning!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-02-2016, 06:03 PM #2
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,938

    Re: Count Overlapping dates in multiple date ranges with a criteria

    Try this in J4 - adjust the ranges to match the rows in A, E, and F

    =SUMPRODUCT(($A$2:$A$36=$I4)*($E$2:$E$36<=J$1)*($F$2:$F$36>=J$1))-1
    Bernie Deitrick
    Excel MVP 2000-2010
    Reply With QuoteRegister To Reply
  3. 12-05-2016, 03:07 PM #3
    jenn.murphy
    jenn.murphy is offline
    Registered User
    Join Date
    12-02-2016
    Location
    Fort Collins, CO
    MS-Off Ver
    2013
    Posts
    2

    Re: Count Overlapping dates in multiple date ranges with a criteria

    Thanks so much! This will work perfectly!
    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. Find count of unique values with multiple criteria inlcuding date ranges
    By jdooley in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-21-2016, 01:05 PM
  2. Count rows with multiple criteria; comparing ranges and ranges to value in one statement
    By chomo in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-30-2016, 07:07 PM
  3. Create Unique Overlapping Date Ranges from List of Dates
    By dbs105 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-11-2015, 08:03 PM
  4. Count number of overlapping days in multiple date ranges
    By tarsonis in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-08-2015, 03:22 PM
  5. [SOLVED] Count number of dates between two date ranges, with two criteria
    By steve@stanley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-30-2013, 10:44 AM
  6. how to count # rows having overlapping date ranges
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-13-2011, 01:54 PM
  7. Count Multiple Criteria within Multiple Date Ranges
    By E6BAV8R in forum Excel General
    Replies: 3
    Last Post: 12-08-2010, 05:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1