Count multiple installation start and end date ranges in each week, of each year

[danquale]

Hello. Can you please help me figure out how to count how many times multiple installation start and end date ranges occur in each week, of each year, between two date columns for each installer?

I'm trying to count how many, let say "installs," occur per week between the start date and end date columns.

For example, if someone 44 different installs per year but have different durations and start and end dates, then how many occur each week at the same time.

The formula I tried:

=SUMPRODUCT(--($H$3:$H$44>=K3),--($G$3:$G$44<=L3),--($A$3:$A$44=$M$2))

[#2]

@danquale 

I took a look at this yesterday when you first posted and decided to leave it for somebody else to sort through the morass of information you provided. I see that at least 38 other people appear to have done the same.

May I suggest to you that you simplify/clarify what you're asking. The question itself appears to be fairly simple, but you're asking an awful lot of any of us who might want to help--two links to google spreadsheets appear in your posting itself, plus two Excel workbooks, each of which has notations in it meant, I'm sure, as directions, some of them from you and some to you....

It's too much.

You need to remember, the people here who want to help are not being paid for the time we take. It's because we enjoy helping people understand what Excel does; not because we are mind-readers or cryptanalysts.

So if you could create a simple workbook that just represents the core installation database, dates of start and end, and whatever else is necessary to focus in on the precise problem for which you're seeking help, I'm sure that one of those 38+ people would be delighted to offer some suggestions.

[#3]

Thanks @mathetes , fully agree

[#4]

Thank you. I simplified the question and attachment @mathetes 

[#5]

Thank you. I simplified the question and attachment @Sergei Baklan 

[#6]

@danquale 

That was quick. Thank you.

And maybe I'm slow to grasp this, but I need a bit more clarification. I'm just looking at Frank Hulett and Derek Lewis as an example in asking this:

1. When I look at the columns headed "Week Start" and "Week End" it looks to me as if there is one per week; there is no overlap on any of the rows. So is that one install per week? Does that hold true for all other names? Why aren't there Week Start and Week End dates for most of them? Those two columns also don't appear to be part of the same "table" as the others; are they? (Column J is empty and separates them from the rest)
2. Nor do I understand what the significance is of those columns headed "Post Install Start Date" and the corresponding "End" date column: are they somehow part of the analysis you're seeking?

I do realize that this is all clear to you, but your data and your question do still require some clarification.

[#7]

Thank you for all your help and quick responses. I greatly appreciate it.

I uploaded a new spreadsheet with the formula I tried to give you a better idea of what I'm trying to accomplish.

However, if I look at how many installs overlap for Deb Condon for the week of 5/17 to 5/23, by manually counting which ones in the "Post Install Start Date," and "Post Install End Date Columns," then I get a manual count of two for the week of 5/17 to 5/23. Whereas the formula, I tried, says four. This is the same for the other rows. The formula seems off by two or more.

The formula I tried:
=SUMPRODUCT(--($H$3:$H$44>=K3),--($G$3:$G$44<=L3),--($A$3:$A$44=$M$2))

@mathetes 

[#8]

@danquale 

I came up with something, but am not very confident. You'll have to look and see if the numbers seem at all reasonable. They look far too unchanging for each person to be realistic. Except that they do change some toward the end of the year. So you have to be the judge.

I added columns for WEEKNUM rather than the dates, just to make things fit into a smaller table; the same formula could work with the dates as you have them.

This formula:

=COUNTA(FILTER($A$3:$A$218,($A$3:$A$218=$S3)*($H$3:$H$218>=T$2)*($J$3:$J$218<=T$2)))

does rely on the new FILTER function, which is one of several very useful Dynamic Array functions. It should retrieve all the rows that meet the criteria enunciated, which are, in order

Name = name at header column

Dates >= start date and <= end date.

Fortunately for both of us, there is always another way in Excel to accomplish a given goal. So maybe one of the wizards around here will come up with another far more effective solution. I'll be following with interest.

P.S.  If you end up posting another version, please do it in conjunction with your new post rather than going up to the start and deleting what was there. It sometimes help to have the whole history, unaltered.

[#9]

Thank you for your efforts and inventiveness. However, based on my manual calculations in the attached spreadsheet, I have different numbers for Deb Condon, for example, in the tab entitled "SE Grid (DEB)." I put these manual numbers together by eyeballing the date ranges of each install and manually counted how many overlapped in each week. @mathetes 

[#10]

@danquale 

I'm not sure I completely understand what you're looking for, but it appears you want to identify the number of jobs for the specific employee that are started on the date(s) in the left column and overlap the weeks across the top column?

I think you may need to just add one more condition to your sumproduct to match the start dates in the data to the start dates in the leftmost  column.

Check this file. There are 3 weeks that are different from your manual calculations, which I highlighted. But, those may just be a mistake in the manual calculations?

[#11]

@danquale 

I think there's something about your data that I'm just not grasping. I have noticed that it appears that each project takes six weeks. Noting that, I tweaked the formula here just a bit and got closer to some of your numbers, but not in the same weeks. Maybe you can make some adjustments to it given your understanding of the data itself.

[#12]

Thank you so much for all your efforts and help. You have been the only person I have found online to even take a stab at this, let alone two. I very much appreciate how generous you have been with your time. The numbers in your second generous attempt still don't quite match what I have just eyeballed in manually adding things up. However, I'm going to look into VBA programming to see if that will provide a resolution@mathetes  

[#13]

@danquale  I just took a look at this and your formula looks correct.  Maybe I don't understand what you are trying to do but it looks like the first 4 jobs overlap that week.  The 1st ending in 2021, the 2nd started in april and ending 5/24 and the next 2 starting 5/4 and going to June.

The second week (5/24/2020 - 5/30/2020) shows 6 which is also correct because line 2 ends on 5/24/2020 and is therefore = to the criteria and you defined it as <=