Forum Discussion
Sum Array based on Date Range (Date Range Changes per Dimension)
Hi, if someone can help me sum as below, I find it difficult to sum by each project's date range, it may need to use array - I can't get it work. (I posted to Power BI forum as well because that is what my team wants, I am thinking I can at least use Excel to get the table if it can't be done in Power BI by Dax )
I want to sum hours linked to no project (=BLANK project), but within each Project's start and end range. And by person, by project.
Sumifs won't work.
This sample data is simple, only has two project in it, so we can point to that specific cell to get that project's time range. But in real data. the Project are hundreds, and the name is like "Timesheet Sept", "Store In the East", random names. So the formula needs to somehow look for the project's start and end date based on its name; then use its start/end date to sum the hours that NOT linked to any project.
Data for copying:
| Data table 1: | ||||
| Name | Hours | Logged Date | Project | Condition |
| Worker A | 0.5 | 12/25/2019 | BLANK | Condition X |
| Worker A | 1 | 1/2/2020 | BLANK | Condition Y |
| Worker A | 2 | 3/20/2020 | BLANK | Condition Y |
| Worker B | 1 | 3/15/2020 | BLANK | Condition Y |
| Worker B | 5 | 3/9/2020 | BLANK | Condition Y |
| Worker B | 1 | 1/1/2020 | Project A | Condition X |
| Worker C | 2 | 3/2/2020 | Project A | Condition Y |
| Worker D | 3 | 4/2/2020 | Project B | Condition X |
| Data table 2: | ||||
| Project Start Date | Project Complete Date | |||
| Project A | 12/20/2019 | 3/10/2020 | ||
| Project B | 1/1/2020 | 4/1/2020 |
Thank you!
6 Replies
In B21: =SUMIFS($B$3:$B$10, $C$3:$C$10, ">="&$B$14, $C$3:$C$10, "<="&$C$14, $E$3$E$10, B$20)
Fill or copy to C21.
In B22:=SUMIFS($B$3:$B$10, $C$3:$C$10, ">="&$B$14, $C$3:$C$10, "<="&$C$14, $E$3$E$10, B$20, $A$3:$A$10, $A22)
Fill or copy to C22, then to row 23.
Similar for B24:C26, but with $B$15 and $C$15.
Hi Hans, sumifs won't work.
This sample data is simple, only has two project in it, so we can point to that specific cell to get that project's time range. But in real data. the Project are hundreds, and the name is like "Timesheet Sept", "Store In the East", random names. So the formula needs to somehow look for the project's start and end date based on its name; then use its start/end date to sum the hours that NOT linked to any project.
Thank you.
Could you attach a small sample workbook without sensitive data that illustrates your problem?
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.
