Function help to lookup and calculate the sum?
.... so did your example which is why I coded it as I did.
Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690
If you only want one figure per workcentre then use SUMPRODUCT
=SUMPRODUCT(--($A$2:$A$21=G2),($C$2:$C$21)+($D$2:$D$21))
where G2 contains the Workcenter ID.
HTH
"Cam" wrote:
Thanks Toppers,
Everything worked except, it lists a duplicate workcenter multiple times.
"Toppers" wrote:
VBA solution.
Assumes input on Sheet1, output on Sheet2
Sub Sumwkctr()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Long, lastrow As Long, orow As Long
Dim sumtime As Double
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
orow = 1
ws2.Cells(orow, 1).Resize(1, 2) = Array("WorkCentre", "Cycle Time")
With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
wkctr = .Cells(2, 1)
sumtime = 0
For r = 2 To lastrow
If .Cells(r, 1) < wkctr Then
orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime
wkctr = .Cells(r, 1)
sumtime = 0
End If
sumtime = sumtime + .Cells(r, 3) + .Cells(r, 4)
Next r
orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime
End With
End Sub
"Cam" wrote:
Hi,
I have an Excel file with the following info. I need to calculate the
cycletime (setup + run time) for each Workceter.
What function I need to lookup at column A, put a workcenter (only once if
multiple) in a cell, then calculate the total cycletime for each workcenter.
Thanks
DATA:
A B C D
1 Workcenter Machine Setup Run time
2 MCB607 5 30
3 XJ2607 5 10
4 QPU607 5 2
5 XM4607 15 5
6 XM4607 296 815 15
7 XM4607 296 5 0
8 XM4607 296 110 15
9 XM4607 5 0
10 XM4607 10 5
11 XM4607 15 5
12 XM4607 296 750 15
13 XM4607 296 100 15
14 XM4607 5 0
15 XM4607 10 5
16 XM8607 943 5 60
17 XJ2607 15 120
18 XJ2607 15 10
19 QPU607 5 2
10 XM6607 495 90 600
Result:
Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690
|