Count number of unique overlap days - Meeting criteria

spydey

Active Member
Joined
Sep 19, 2017
Messages
314
Office Version
  1. 2013
Platform
  1. Windows
I have scoured online for hours now and tried a number of things. Each one seems to get me close, but no cigar.

I am turning to you all for some assistance.

I have 2 sheets.

First sheet, Items, contains 4 columns:
  • Item
  • Start Date
  • End Date
  • # Days

The Item is unique and will only appear once.

Second sheet, Events, contains 4 columns:
  • Item
  • Event Name
  • Event Start Date
  • Event End Date

The Item in the second sheet could be there multiple times. The combination of Item, Event Name, and Event Start/Ending date will always be unique.

I am trying to get a count of unique # of days for each item in the Items sheet (results to be placed in column "# of Days"), from the Events sheet where the event start and/or end dates for that item in the Events sheet, fall on or between the item's start/end dates from the Items sheet.

I say unique because I don't want to double count days from the events.

Example:

If one event started on 01/01/2018 and ended on 01/10/2018, that would be 10 days (counting the starting day/date).
If a different event but same item, started on 01/05/2018 and ended on 01/15/2018, that would be 11 days (counting the starting day/date)
But in total, between the two events, it would be 15 unique days, not 21 days, due to the overlap of 01/05/2018 to 01/10/2018. I don't want to double count. I hope that makes sense.

I have tried a number of things but can't seem to get exactly what I what/need.

Here are some things I have tried:

https://www.get-digital-help.com/2015/05/18/count-overlapping-days-in-multiple-date-ranges/
https://www.get-digital-help.com/2015/08/12/count-overlapping-days-across-multiple-date-ranges/
https://www.get-digital-help.com/2013/04/26/mmult-function-matrix-multiplication/
https://wmfexcel.com/2014/10/25/how-to-calculate-number-of-overlapping-days-for-two-periods/


I have a sample file shared in my GDrive.

https://drive.google.com/file/d/1HBgr-KITlXvxL_n_5i1GWOj5SwoZeI3I/view?usp=sharing

Any help, suggestion, etc, that anyone can provide is greatly appreciated.

My actual file has several thousand unique items, and several thousand events, so I need something that will allow me to apply it with that in mind.

Again, thank you for your time!!

-Spydey
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Is this what you are looking for?


Book1
ABCD
1ItemStart DateEnd Date# Days
2Shoes05-01-201830-06-2018177
3Hat25-09-201727-09-2018368
4Socks09-05-201825-10-2018170
5Pants01-01-201831-12-201846
6Shirt13-08-201623-01-2018529
7
Items
Cell Formulas
RangeFormula
D2{=SUM(--(MMULT((ROW(INDIRECT(B2&":"&C2))>=TRANSPOSE(IF(Events!$A$2:$A$19=A2,Events!$C$2:$C$19)))*(ROW(INDIRECT(B2&":"&C2))<=TRANSPOSE(IF(Events!$A$2:$A$19=A2,Events!$D$2:$D$19))),Events!$C$2:$C$19^0)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
@Nishant94

Thank you for the assistance.

I think that something like this will work.

I do have a few more questions, if I may.

My actual data file I am working with doesn't have the Items tab start and end dates in adjoining columns. They are not right next to each other. They are separated by several columns.

I see in the formula you provided (thank you again) that you have referenced Indirect(B2&":"&C2) for the start and end dates.

With there being several columns between them, how can I adjust this part of the formula so that I am capturing the correct start & end date cells/row/columns?

Also, my actual data file, the events tab, the events are not ordered neatly by the events columns. They are all mixed around. Will that matter? Will the formula still function if the events tab data is not in any particular order, meaning the items are not in any order?

Thank you again for your help and assistance.

-Spydey
 
Upvote 0
@Nishant94

So I tested out the formula to see if the order of the data in the Events tab mattered, and it doesn't.

I guess it is because of your IF statements in the formula. They are only using the event start and event end dates if the corresponding item from the events tab matches the item from the items tab for the row in the items tab where we are placing the results. Is that right?

I am trying to dissect the formula. It is very similar to one that I found online and tried to use but it required that data be continuous and didn't account for subsets (meaning different items).

The remaining thing I need to figure out is, how to change the formula so that it will capture the correct item start date and end date from the items tab, when those two dates are not side by side. They will always be on the same row but not in columns side-by-side.

Example:

For the Items tab.

Items could be in column B
Start could be in column G
End could be in column W

Any ideas on that?

-Spydey
 
Upvote 0
The remaining thing I need to figure out is, how to change the formula so that it will capture the correct item start date and end date from the items tab, when those two dates are not side by side. They will always be on the same row but not in columns side-by-side.

Example:

For the Items tab.

Items could be in column B
Start could be in column G
End could be in column W


I wonder if this even matters?

-Spydey
 
Upvote 0
I wonder if this even matters?

-Spydey

Just tested it and it doesn't seem to matter which columns the start & end dates are in from the Items tab, as long as I correctly reference them in within the indirect function. MAGNIFICENT!!!

Would you mind breaking down the formula and explaining it to me? I have been trying to but am a little stuck on a few things. I would really like to understand how it is all working together.

Thank you again!!

-Spydey
 
Upvote 0

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top