Example.xlsx
=SUM(IF(NOT($K$12:$K$16=0),INDEX($B$34:$C$36,MATCH($E$12:$E$16,$B$34:$B$36,0),2)*IF($G$12:$G$16="OT",1.5,1)*$I$12:$I$16,0))
=SUM(INDEX($C$14:$D$15,N(IF(1,MATCH($F$4:$F$5,$C$14:$C$15,0))),2))
Example2.xlsx
Function TotalLaborCost(oRng As Range, vRng As Range) As Double
Dim x, y
Dim i As Long, j As Long
Dim Total As Double, n As Double
x = oRng.Value
y = vRng.Value
For i = 1 To UBound(x, 1)
If x(i, 1) <> "" Then
For j = 1 To UBound(y, 1)
If x(i, 1) = y(j, 1) Then
If x(i, 2) = "ST" Then
n = 1
Else
n = 1.5
End If
Total = Total + x(i, 3) * y(j, 2) * n
End If
Next j
End If
Next i
TotalLaborCost = Total
End Function
Then use the above function on the sheet like this....=TotalLaborCost(F4:H8,C14:D17)
For more details, refer to the attached.Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!