Sum cells based on Time and Category

[deante]

I hope all is Well! I have been trying to come up with a worksheet function that would eliminate some tedious data crunching.

I need to sum the Duration (column D), if column A and Column B are matched between the the rows. Th tricky part is they only need to sum if the start time are within 3 hours (.125 as excel time number). Also, there can be as many as 6 items that need to be summed but all 6 items need to be within the 3 hour time frame.

I have tried several variations of worksheet functions, but have been unsucessful. I will use a macro but I dont even know where to start.

Thanks in Advance,

DeAnte


COLUMN A COLUMN B COLUMN C COLUMN D
Cause1Name Cause2Name StartDate Duration
STARTUP NORMAL STARTUP 1/5/2009 12:15 4.2
STARTUP NORMAL STARTUP 1/5/2009 12:19 12.3
STARTUP START-UP 1/5/2009 12:32 0.5
CHANGEOVER FLAVOR CHANGE 1/5/2009 19:18 10.0
CHANGEOVER FLAVOR CHANGE 1/6/2009 4:09 3.9
SANITATION SHORT SANITATION1/6/2009 5:56 40.7
SANITATION SHORT SANITATION1/6/2009 6:37 0.6
CHANGEOVER FLAVOR CHANGE 1/6/2009 6:42 11.0
CHANGEOVER FLAVOR CHANGE 1/6/2009 9:39 15.0
CHANGEOVER FLAVOR CHANGE 1/6/2009 13:08 60.3
CHANGEOVER FLAVOR CHANGE 1/6/2009 14:08 8.3

 

[SkipVought]

Hi,

within 3 hours of WHAT?

What do you expect the results to be, based on your example?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[deante]

The results should be within 3 hours of the first event of that Cause 1 type. For the data shown, the desired result is below. Notice how the flavor changes were not combined because the time between events is over 3 hours.

I am trying to combine events that have been broken out into seperate events. Thanks for your help.

DeAnte


Cause1Name Cause2Name StartDate Duration
STARTUP NORMAL STARTUP 1/5/2009 12:15 17
CHANGEOVER FLAVOR CHANGE 1/5/2009 19:18 10.0
CHANGEOVER FLAVOR CHANGE 1/6/2009 4:09 3.9
SANITATION SHORT SANITATION 1/6/2009 5:56 41.3
CHANGEOVER FLAVOR CHANGE 1/6/2009 6:42 26.0
CHANGEOVER FLAVOR CHANGE 1/6/2009 13:08 68.6

 

[SkipVought]

And the answer to the SECOND question?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

OK
[tt]
Cause1Name Cause2Name StartDate Duration CalcDur
STARTUP NORMAL STARTUP 1/5/2009 12:15 4.2 16.5
STARTUP NORMAL STARTUP 1/5/2009 12:19 12.3
STARTUP START-UP 1/5/2009 12:32 0.5 0.5
CHANGEOVER FLAVOR CHANGE 1/5/2009 19:18 10 10
CHANGEOVER FLAVOR CHANGE 1/6/2009 4:09 3.9
SANITATION SHORT SANITATION 1/6/2009 5:56 40.7 41.3
SANITATION SHORT SANITATION 1/6/2009 6:37 0.6
CHANGEOVER FLAVOR CHANGE 1/6/2009 6:42 11 39.9
CHANGEOVER FLAVOR CHANGE 1/6/2009 9:39 15
CHANGEOVER FLAVOR CHANGE 1/6/2009 13:08 60.3
CHANGEOVER FLAVOR CHANGE 1/6/2009 14:08 8.3
[/tt]
[tt]
=SUMPRODUCT((Cause1Name=A2)*(Cause2Name=B2)*(StartDate<=C2+0.125)*(Duration))
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I think that this will work better...
[tt]
=IF(A2&B2=A1&B1,"",SUMPRODUCT((A2:$A$12=A2)*(B2:$B$12=B2)*(C2:$C$12<=C2+0.125)*(D2:$D$12)))
[/tt]
Paste in E2 and copy down.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[deante]

The second formula worked. However, I want the cells that are not first one. to have no value. Im trying some other formulas but have had no luck

 

[SkipVought]

I want the cells that are not first one. to have no value

That's EXACTLY what that last formula does!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]