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:D$8)


"Cam" wrote:

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



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hi Valko,

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


"T. Valko" wrote:

Try this:

With this data on Sheet2 in the range A2:D8

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:D$8)


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
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