Sum Array based on Date Range (Date Range Changes per Dimension)

%3CLINGO-SUB%20id%3D%22lingo-sub-1823343%22%20slang%3D%22en-US%22%3ESum%20Array%20based%20on%20Date%20Range%20(Date%20Range%20Changes%20per%20Dimension)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823343%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20if%20someone%20can%20help%20me%20sum%20as%20below%2C%20I%20find%20it%20difficult%20to%20sum%20by%20each%20project's%20date%20range%2C%20it%20may%20need%20to%20use%20array%20-%20I%20can't%20get%20it%20work.%20(I%20posted%20to%20Power%20BI%20forum%20as%20well%20because%20that%20is%20what%20my%20team%20wants%2C%20I%20am%20thinking%20I%20can%20at%20least%20use%20Excel%20to%20get%20the%20table%20if%20it%20can't%20be%20done%20in%20Power%20BI%20by%20Dax%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20sum%20hours%20linked%20to%20no%20project%20(%3DBLANK%20project)%2C%20but%20within%20each%20Project's%20start%20and%20end%20range.%20And%20by%20person%2C%20by%20project.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image%203.png%22%20style%3D%22width%3A%20769px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229537iB1AC6820B92D52DE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image%203.png%22%20alt%3D%22Image%203.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1823343%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1823433%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Array%20based%20on%20Date%20Range%20(Date%20Range%20Changes%20per%20Dimension)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823433%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847655%22%20target%3D%22_blank%22%3E%40VivianC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20sounds%20like%20a%20perfect%20instance%20for%20the%20use%20of%20Excel's%20%3CSTRONG%3EPivot%20Table%3C%2FSTRONG%3E%20capability.%20Have%20you%20tried%20that%3F%20The%20Pivot%20Table%20can%20be%20refined%20in%20any%20number%20of%20ways.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20easier%20to%20answer%20if%20you%20attached%20a%20representative%20sample%20of%20your%20real%20data%2C%20so%20long%20as%20no%20confidential%20information%20is%20included.%20The%20image%20you%20provided%20is%20what%20I%20based%20my%20assessment%20above%20on%3B%20to%20go%20further%2C%20an%20actual%20working%20spreadsheet%20would%20be%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1823434%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Array%20based%20on%20Date%20Range%20(Date%20Range%20Changes%20per%20Dimension)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847655%22%20target%3D%22_blank%22%3E%40VivianC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B21%3A%20%3DSUMIFS(%24B%243%3A%24B%2410%2C%20%24C%243%3A%24C%2410%2C%20%22%26gt%3B%3D%22%26amp%3B%24B%2414%2C%20%24C%243%3A%24C%2410%2C%20%22%26lt%3B%3D%22%26amp%3B%24C%2414%2C%20%24E%243%24E%2410%2C%20B%2420)%3C%2FP%3E%0A%3CP%3EFill%20or%20copy%20to%20C21.%3C%2FP%3E%0A%3CP%3EIn%20B22%3A%3DSUMIFS(%24B%243%3A%24B%2410%2C%20%24C%243%3A%24C%2410%2C%20%22%26gt%3B%3D%22%26amp%3B%24B%2414%2C%20%24C%243%3A%24C%2410%2C%20%22%26lt%3B%3D%22%26amp%3B%24C%2414%2C%20%24E%243%24E%2410%2C%20B%2420%2C%20%24A%243%3A%24A%2410%2C%20%24A22)%3C%2FP%3E%0A%3CP%3EFill%20or%20copy%20to%20C22%2C%20then%20to%20row%2023.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESimilar%20for%20B24%3AC26%2C%20but%20with%20%24B%2415%20and%20%24C%2415.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1829157%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Array%20based%20on%20Date%20Range%20(Date%20Range%20Changes%20per%20Dimension)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1829157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Hans%2C%20sumifs%20won't%20work.%3C%2FP%3E%3CP%3EThis%20sample%20data%20is%20simple%2C%20only%20has%20two%20project%20in%20it%2C%20so%20we%20can%20point%20to%20that%20specific%20cell%20to%20get%20that%20project's%20time%20range.%20But%20in%20real%20data.%20the%20Project%20are%20hundreds%2C%20and%20the%20name%20is%20like%20%22Timesheet%20Sept%22%2C%20%22Store%20In%20the%20East%22%2C%20random%20names.%20So%20the%20formula%20needs%20to%20somehow%20look%20for%20the%20project's%20start%20and%20end%20date%20based%20on%20its%20name%3B%20then%20use%20its%20start%2Fend%20date%20to%20sum%20the%20hours%20that%20NOT%20linked%20to%20any%20project.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

Image 3.png

 

Data for copying:

Data table 1:    
NameHoursLogged DateProjectCondition
Worker A0.512/25/2019BLANKCondition X
Worker A11/2/2020BLANKCondition Y
Worker A23/20/2020BLANKCondition Y
Worker B13/15/2020BLANKCondition Y
Worker B53/9/2020BLANKCondition Y
Worker B11/1/2020Project ACondition X
Worker C23/2/2020Project ACondition Y
Worker D34/2/2020Project BCondition X
     
Data table 2:    
 Project Start DateProject Complete Date  
Project A12/20/20193/10/2020  
Project B1/1/20204/1/2020  

Thank you!

6 Replies

@VivianC 

 

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.

 

 

@VivianC 

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.

@Hans Vogelaar 

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.

@mathetes 

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!

@VivianC 

 

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.

@VivianC 

Could you attach a small sample workbook without sensitive data that illustrates your problem?