Forum Discussion
Sum Array based on Date Range (Date Range Changes per Dimension)
It sounds like a perfect instance for the use of Excel's Pivot Table capability. Have you tried that? The Pivot Table can be refined in any number of ways.
It would be easier to answer if you attached a representative sample of your real data, so long as no confidential information is included. The image you provided is what I based my assessment above on; to go further, an actual working spreadsheet would be helpful.
Hi Mathetes, pivot table won't work. I tried - it goes back to the question I raised. Pivot table calculation is based on what get put into "Rows" section. Please understand I can't ADD each project start and end date to the Table 1 because each project's time range can overlap each other, and the final calculation counts the overlap (take a look at my final table, hours for Worker A/B linked to NO project needs to be counted to both projects if the hours fall into each project's start and end).
And because in real data, the projects are hundreds with random name, so sumifs won't work - I need somehow to look for the project's name first, get its start/end date, then calculated based on the start/end date; then for the next row, do it again because the project's name changes.
I will take your advice though and attach the data here so people can copy to Excel. Thank you!
You're clearly quite a competent Excel user, I'd say. I'm finding it very difficult, though, with the very limited sample data you've provided, to understand the full nature of what you're trying to get done and why it is that Pivot Tables (given its various filtering and different ways to nuance) don't serve. Similarly with SUMIFS...
I have no doubt that you yourself are clear in your ow mind, but were we to sit down face-to-face, we'd have access to more of the data, and you could sketch out the result you're looking for. It's hard, just using words, to get there, especially communicating with people who aren't familiar with the situation.
That sample data just isn't sufficient. Is it not possible to post the actual spreadsheet(s), devoid of actual information that is privileged? Or, at the very least, a far more fully representative set of data. And please, post an actual Excel sheet, not data to be copied....that just adds a step and kind of serves as a disincentive to getting the help you're seeking. If you could also give a clearer illustration of what the output might be from that raw data....that too would serve to help.