Finding Total Duration Based on Multiple Criteria

[Orion19]

Hello all, and Thank You in advance!

A sample data set is below. I'm trying to write a formula that tells me the total session length for any given date. In the data set below, the output I'm looking for would be 5:17 (3:04 + 2:13). There are only two sessions, one from 8:29 to 11:33 and the second from 12:47 to 15:00. Projects (name and quantity), Start Times, and End Times will all vary. All of the attempts I've made have failed to recognize there are only two sessions in an example like this. Thanks again for any tips!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Project[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Session Length[/TD]
[/TR]
[TR]
[TD]Project 1
[/TD]
[TD]5/1[/TD]
[TD]8:29[/TD]
[TD]11:33[/TD]
[TD]3:04[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]5/1[/TD]
[TD]12:47[/TD]
[TD]15:00[/TD]
[TD]2:13[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]5/1[/TD]
[TD]8:29[/TD]
[TD]11:33[/TD]
[TD]3:04[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]5/1[/TD]
[TD]12:47[/TD]
[TD]15:00[/TD]
[TD]2:13[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]5/1[/TD]
[TD]8:29[/TD]
[TD]11:33[/TD]
[TD]3:04[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]5/1[/TD]
[TD]12:47[/TD]
[TD]15:00[/TD]
[TD]2:13[/TD]
[/TR]
</tbody>[/TABLE]

 

[Orion19]

A huge of number of this kind of formulas can cause some delay, especially if you also have formulas around with volatile functions like INDIRECT, OFFSET.

Does the data range change often? If it does, we can work with dynamic named ranges. If you want try this route, would you post the formula as you have implemented it?

We pull reports that download as a CSV. Each data set we download is formatted the same but the range is different. The data set doesn't change often when referencing a single workbook but the data range from one workbook to the next can be very different. The biggest issue I see is the delay that will be caused when trying to filter the raw data (if we wanted to see a specific date, for example). The macro I'm working on needs to address all data sets we download. Does that answer your question?

Here is the formula as it appears in the first cell reference:

=IFERROR(SUM(IF(FREQUENCY(IF(RawData!$D$2:$D$5000=Home!E7,MATCH(RawData!$H$2:$H$5000,RawData!$H$2:$H$5000,0)),ROW(RawData!$D$2:$D$5000)-ROW(RawData!$D$2)+1),RawData!$J$2:$J$5000)),"")

 

[Aladin Akyurek]

We pull reports that download as a CSV. Each data set we download is formatted the same but the range is different. The data set doesn't change often when referencing a single workbook but the data range from one workbook to the next can be very different. The biggest issue I see is the delay that will be caused when trying to filter the raw data (if we wanted to see a specific date, for example). The macro I'm working on needs to address all data sets we download. Does that answer your question?

Here is the formula as it appears in the first cell reference:

=IFERROR(SUM(IF(FREQUENCY(IF(RawData!$D$2:$D$5000=Home!E7,MATCH(RawData!$H$2:$H$5000,RawData!$H$2:$H$5000,0)),ROW(RawData!$D$2:$D$5000)-ROW(RawData!$D$2)+1),RawData!$J$2:$J$5000)),"")

Well, the delay seems to be mostly due to the filtering actions. No idea how that can be speeded up.

 

[Peter_SSs]

Here is the formula as it appears in the first cell reference:

=IFERROR(SUM(IF(FREQUENCY(IF(RawData!$D$2:$D$5000=Home!E7,MATCH(RawData!$H$2:$H$5000,RawData!$H$2:$H$5000,0)),ROW(RawData!$D$2:$D$5000)-ROW(RawData!$D$2)+1),RawData!$J$2:$J$5000)),"")

1. What sheet and what cell is that formula in?

2. I haven't been following this thread too closely, so is the question still the same as in post #1 ? That is, finding the total duration for a particular date?

3. It seems that the layout is not quite the same as what you posted earlier, so can you post another small set of sample data (for 'RawData') and ensure that we can tell what columns are what?

 

[Orion19]

1. What sheet and what cell is that formula in?

2. I haven't been following this thread too closely, so is the question still the same as in post #1 ? That is, finding the total duration for a particular date?

3. It seems that the layout is not quite the same as what you posted earlier, so can you post another small set of sample data (for 'RawData') and ensure that we can tell what columns are what?

Hi!

1. The formula is in cell H7 on a tab titled "Home". The formula references "RawData" in a separate tab. I've dragged the formula down through cell H500 on the Home tab.

2. The formula answers my original question but the impact it had on filtering is why I followed up (but I think I've figured out what's going on).

3. I can work on that. It's a fairly large data set and there are some privacy concerns but I'll figure it out. I think what's going on is that the formula references almost 5000 cells in columns D, H, and J of the RawData. I did that because the size of the data set will change and I wanted to ensure that all data is captured by the formula. This results in a lot of unnecessary calculations and seems to be what slows the filter down. I've experimented with named ranges that only reference rows that actually have data and that seems to fix it, but I'm still trying to figure out a way to do that programmatically without referring to specific cells. For example, this works when naming the entire raw data set:

Range("A1").CurrentRegion.Select
Range("A1").CurrentRegion.Name = "RawDataSet"

Something like that would work because it doesn't reference specific cells. However, I haven't figured out how to do that for individual columns. I can hold command+shift+down (I'm on a mac) and it'll highlight the range but the code ends up referencing specific cells so it won't work. If you have any ideas I'm all ears. Thanks!!!

 

[Peter_SSs]

If you have any ideas I'm all ears.

I would be looking at a macro alternative, though without testing I don't know if performance will be better or significantly better. I also do not use a Mac so I don't know if my idea will work for you but would be worth a try.

To do that testing I would like some sample data, including layout, so that I can be more confidant the code won't need more modification because your layout is different to mine. Privacy concerns should not be a problem because I only need a fairly small sample and you can just use dummy data, so long as it is representative of your real data in terms of layout and type (eg text/numerical)