lookup formula to sum percent

[oxicottin]

Hello, I have been trying to figure out how to do this for two weeks and I'm lost.... Lets start off With my TEST sheet and Employee 1. What I need it to do is if I enter an Act # (1) which is an "Activity Number" into cell A5 and a Date (1/26/18) into B5 I want it to lookup the corresponding data in the Safety Activity "Legend" at the bottom and display the Act % "Activity %" in Employee 1's B25 which would be .4.

Should give Safety Activities Act #.... =VLOOKUP(A5,A30:B36,1,0)
Should gives Safety Activities Act %.... =VLOOKUP(A5,$A$30:$AB$36,28,0)

The problem I have is there is a limit "Activity Limit" to the times a Act # can be performed. So using the example above I used Act # 1 and its Activity Limit is 2 so if an employee enters it twice lets say A5 on 1/26/18 and A6 on 1/27/18 they reached their limit so how do I make it not be able to add an .4 in if someone enters another Act 1 lets say on A7 1/28/18 I want them to get zero percent for that because they reached their limit.

[Paul_Hossler]

I'd make a user defined function since IMHO the logic is too complicated for just worksheet formulas

IF I UNDERSTAND your logic ...




Option Explicit

Function ActivityPercent(actNumbers As Range, ActLimits As Range) As Double
Dim i As Long
Dim actNumber As Variant, limitNumber As Variant, limitMax As Variant, limitPercent As Variant
Dim actCount() As Long
Dim actTotal As Double

Application.Volatile

'move data into arrays
With Application.WorksheetFunction
actNumber = .Transpose(actNumbers.Columns(1))
limitNumber = .Transpose(ActLimits.Columns(1))
limitMax = .Transpose(ActLimits.Columns(2))
limitPercent = .Transpose(ActLimits.Columns(28))
End With


'create and init a max count array
ReDim actCount(LBound(limitNumber) To UBound(limitNumber))
For i = LBound(limitMax) To UBound(limitMax)
actCount(i) = limitMax(i)
Next I

'see if activity count exceeded by decrementing max
For i = LBound(actNumber) To UBound(actNumber)
If actNumber(i) > 0 Then
If limitMax(actNumber(i)) > 0 Then
limitMax(actNumber(i)) = limitMax(actNumber(i)) - 1
actTotal = actTotal + limitPercent(actNumber(i))
End If
End If
Next I

ActivityPercent = actTotal

End Function

[oxicottin]

Spot on... Thank you!