CODEWITHSUNDEEP
vbaexcel
J Bree
J Bree

Reputation: 7

Excel VBA Sumif function

I have a working script that I need to add a sumif to. I think you should be able to see where I'm trying to enter the formula. I am trying to reference another sheet in the same workbook. The section I'm stuck on is if the compPlan = L.

Sub CalculateCommission()
    Dim Index As Integer    'Row Index
    Dim employeeName As String 'A
    Dim compPlan As String  'B
    Dim baseWage As Double  'AM
    Dim commAN As Double 'AN
    Dim guarantee As Double 'AO
    Dim earningsDue As Double   'AQ
    Dim priorPay As Double 'AR from prior pay cycle
    Dim priorBase As Integer 'AS from prior pay cycle
    Dim newComm As Double   'AU
    Dim packBack As Double 'AP

    Index = 3

    Do While Len(ActiveSheet.Range("B" & Index))
        employeeName = UCase(ActiveSheet.Range("A" & Index))
        compPlan = UCase(ActiveSheet.Range("B" & Index))
        baseWage = ActiveSheet.Range("AM" & Index)
        newComm = ActiveSheet.Range("AU" & Index)
        earningsDue = ActiveSheet.Range("AQ" & Index)
        guarantee = ActiveSheet.Range("AO" & Index)
        packBack = ActiveSheet.Range("AP" & Index)


        If (IsError(ActiveSheet.Range("AR" & Index)) = False) Then
            priorPay = ActiveSheet.Range("AR" & Index)
        Else
            priorPay = 0
        End If

        If (IsError(ActiveSheet.Range("AS" & Index)) = False) Then
            priorBase = ActiveSheet.Range("AS" & Index)
        Else
            priorBase = 0
        End If

        commAN = ActiveSheet.Range("AN" & Index)


        If compPlan = "B" Or compPlan = "D" Or compPlan = "E" Or compPlan = "I" Or compPlan = "J" Or compPlan = "L" Then
                commAN = earningsDue - baseWage - guarantee - priorBase
            Else
                commAN = earningsDue - baseWage - guarantee
        End If


        If compPlan = "L" Then
                Sumact = Application.SumIf 'Calc by loan'!C:D,"Salesperson",'Calc by loan'!D:D)
        End If



        If compPlan = "B" Or compPlan = "D" Or compPlan = "E" Or compPlan = "I" Or compPlan = "J" Then
            If baseWage + newComm > baseWage Then
                earningsDue = baseWage + newComm + guarantee + priorBase - priorPay + packBack
            Else
                earningsDue = baseWage + guarantee + packBack
            End If
        Else
            If newComm > baseWage + priorPay Then
                earningsDue = newComm - priorPay + guarantee + packBack
            Else
                earningsDue = baseWage + guarantee + packBack
            End If
        End If
        ActiveSheet.Range("AQ" & Index).Value = earningsDue
        Index = Index + 1
    Loop
EndSub:
End Sub

Upvotes: 0

Views: 1537

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You would use it like this:

Application.WorksheetFunction.SumIf(Worksheets("Calc by loan").Range("C:C"),"Salesperson",Worksheets("Calc by loan").Range("D:D"))

Upvotes: 2

Related Questions