Help with calculating time

[Cam]

Hello,

I have an Excel to track performance. Sheet1 is performance table where user
input their ID, date, part#, side, start sequence & stop sequence daily. Then
on a reference sheet2 with field: part#, side, sequence#, time (calculated).

What I would like to calculate is look at the start & stop sequence based on
the part# & side then calculate how long (time) the operator spend on the job
(part#). Thanks

Example:
Sheet1 - User input data
ID date part# side start seq stop seq
001 1/8/08 1360-1 1st 100 500
002 1/10/08 1560-3 2nd 600 1000

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 15
1360-1 1st 600 60
1560-3 2nd 100 10
...........

Result
Sheet3
ID date part# side start seq stop seq Total time
(calculated)
001 1/8/08 1360-1 1st 100 500 70

How should i go about linking the two sheets so it will calculate the time
based on when he start the sequence to when he stop the sequence? Thanks

 

[T. Valko]

Try this:

With this data on Sheet2 in the range A28

1360-1...1st...100...5
1360-1...1st...200...10
1360-1...1st...300...10
1360-1...1st...400...30
1360-1...1st...500...15
1360-1...1st...600...60
1560-3...2nd...100...10

With this data on Sheet3 in the range A2:F2

001...1/8/08...1360-1...1st...100...500

Enter this formula on Sheet3 G2:

=SUMPRODUCT(--(Sheet2!A$2:A$8=C2),--(Sheet2!B$2:B$8=D2),--(Sheet2!C$2:C$8>=E2),--(Sheet2!C$2:C$8<=F2),Sheet2!D$2$8)

 

[Cam]

Hi Valko,

I tried the formula you suggested, but it returns "0" where it shouldn't be.

 

[T. Valko]

You'll have to post a sample of data and show where the formula is returning
a 0.

 

[Cam]

Valko,

I got the results, but the calculation is off little. The calculated cell
are in column G of sheet1. The last info is what it should be calculated
values. For example, sheet1 row 2 should be equal 55 cause it see start (seq)
to stop (seq) is 100 to 400 so it should sum all value from row 2 to 5
(5+10+10+30) in column D from Ref sheet.

Sheet1 - Data sheet
A B C D E F G
ID date part# side start stop Cal Time Should be:
1 1/8/08 1360-1 1st 100 400 50 55 (5+10+10+30)
2 1/10/08 1360-1 1st 300 500 125 65 (10+30+25)
3 1/10/08 1360-1 1st 100 600 135 140 (5+10+10+30+25+60)

Ref - Ref sheet
A B C D
part# side seq time
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10

 

[Cam]

Valko,

Also forgot to mentioned that column G in sheet1 result is the calculated
values based on your sugguested formula. For cell G2 in sheet1
=SUMPRODUCT(--(Ref!A$2:A$8=C2),--(Ref!B$2:B$8=D2),--(Ref!C$2:C$8>=E2),--(Ref!C$2:C$8<=F2),Ref!D$2$8)

 

[T. Valko]

See your other post where I included a screencap.

In my test file I'm getting the *correct* results:

Should be:
55 (5+10+10+30)
65 (10+30+25)
140 (5+10+10+30+25+60)