Sum Formula to exclude days that overlap in range?

[zpeedd3mon]

Good Afternoon Community! I have been helped countless times thru all of the posts on this forum (THANK YOU!) but I'm truly stuck this time. I don't have any experience with VBA so I'm trying to accomplish this all using formulas. Here's the problem:


Problem:
I'm trying to set up a Time in Motion study worksheet to see all of the resource needs for our operation over the course of a year. The operation is a farm and there are pieces of equipment that are used in several different areas with different but sometimes overlapping timeframes. My solution is to take the time frame for each task, count the days in between the start and finish dates excluding Sunday, sum the total days (window of opportunity or WOO) and divide by the capacity of one machine to give me the total number of machines needed to accomplish the task within the given WOO. The problem is that there are some tasks that the WOO overlaps and if I do a simple SUM then the WOO may seem much larger than it truly is. For example, if I have 2 different tasks with 150 days in the WOO then it would show 300 WOO and my equipment needs will be 1/2 of what I actually need.


Setup:
I have a master "Equipment" list that holds the equipment specs, capacity, and a timeline of use that comes from: about 6 other sheets specific to a crop. Each of those crop sheets contains an activity timeline with a start and finish date (WOO) and each activity is tied to a piece of equipment from the "Equipment List." The timeline on the master "Equipment" list is populated by the total days of work from the 6 crop sheets. I need a SUM formula that excludes any days that overlap for all of the crop sheets.

Here's the formula I have now:

=IFERROR((SUM(SUMIFS(potatotasks[Acres/Day To Finish],potatotasks[Impliment],$A41,potatotasks[Start Date],"<="&Z$2,potatotasks[End Date],">="&Z$2),SUMIFS(beetstasks[Acres/Day To Finish],beetstasks[Impliment],$A41,beetstasks[Start Date],"<="&Z$2,beetstasks[End Date],">="&Z$2)))/[@[Acre Capacity of Total Impliments]],0)


Screen Shots:
Equipment Master List - http://grant4dfarms.com/publicforums/equipmentmaster.jpg
Crop Task Template - http://grant4dfarms.com/publicforums/croptasktemplate.jpg


Thanks for any help that you can give me!

-Taylor

 

[zpeedd3mon]

Still working on this and have found a solution that counts the number of overlapping days if the criteria matches "DMI" but now I'm not sure how to expand this formula to not only look at the table "potatotasks" to looking at 2 tables, "potatotasks" and "beetstasks". Does anyone have any thoughts? Here's where I'm at now:

=SUM(IF(FREQUENCY(IF(potatotasks[Impliment]=F36,IF((MIN(potatotasks[Start Date])+COLUMN(OFFSET($A$1,,0,,MAX(potatotasks[End Date])-MIN(potatotasks[Start Date])+1))-1<=potatotasks[End Date])+(MIN(potatotasks[Start Date])+COLUMN(OFFSET($A$1,,0,,MAX(potatotasks[End Date])-MIN(potatotasks[Start Date])+1))-1>=potatotasks[Start Date])>1,MIN(potatotasks[Start Date])+COLUMN(OFFSET($A$1,,0,,MAX(potatotasks[End Date])-MIN(potatotasks[Start Date])+1))-1,"")),MIN(potatotasks[Start Date])+COLUMN(OFFSET($A$1,,0,,MAX(potatotasks[End Date])-MIN(potatotasks[Start Date])+1))-1)>1,1,0))

Tables set up like below:

potatotasks table
Implement Start Date End Date
DMI 1/1 1/10
Plow 1/5 1/20
DMI 1/5 1/20

Beetstasks table2
Implement Start Date End Date
DMI 1/5 1/20
Plow 1/5 1/20
DMI 1/15 1/25