Forum Discussion
Count multiple installation start and end date ranges in each week, of each year
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))
12 Replies
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.
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:
- 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)
- 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.
Thanks mathetes , fully agree
Thank you. I simplified the question and attachment SergeiBaklan
