Function help to lookup and calculate the sum?
Assuming data on Sheet1 is columns A to D and same on Sheet2 A to D , then
on Sheet2:
in A2: first Workcenter ID
In B2: =SUMPRODUCT(--($A2=Sheet1!$A$2:$A$21),--(Sheet1!$C$2:$C$21))
in C2: =SUMPRODUCT(--($A2=Sheet1!$A$2:$A$21),--(Sheet1!$D$2:$D$21))
in D2: =B2+C2
Copy all these formulae down for the end of entries in column A
Change (length of )ranges to suit your data.
HTH
"Cam" wrote:
Hi Toppers again,
Sorry, I didn't make this clear. The result on worksheet 2 I want is:
Workcenter Setup Run time Cycle time
MCB607 5 30 35
QPU607 10 4 14
XJ2607 35 140 175
XM4607 2920 120 3040
XM6607 90 90 180
XM8607 5 5 10
where QPU607, XJ2607, XM4607, etcc (workcenter w/ multiple row only appear
once). What can I do to change to code to give me the result. Thanks
"Toppers" wrote:
... 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
|