Thread: VBA Function Formula Using Sum and If

I am using Excel 2007 on Windows XP. I have the following formula that I would like to move from the worksheet into VBA. I am having an issue with the “IF” statement within VBA throwing a compile error for “Expected: expression”. I am assuming it does not like the “IF” statement within the formula. I also provided a sample of the code that I was able to get working with a SUMIF statement but with only one condition. Any help would be appreciated. Thank you.

Code:

=SUM(IF(Log!$A:$A=$A3,IF(Log!$B:$B=$B3,Log!$H:$H)))

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Code:

Function LOE(ProjectID As Double, ProjectIndicators As String)
    Dim xProjectIndicators As String, CurrentProjectIndicator As String
    Dim zPosition As Long, yLengthOfIndicator As Long
    Dim Sumact As Range

    xProjectIndicators = ProjectIndicators
    yLengthOfIndicator = Len(xProjectIndicators)
    Set logFirstProjID = Worksheets("Log").Range("A1:A50000")
    Set logProjectIndicator = Worksheets("Log").Range("B1:B50000")
    Set logHours = Worksheets("Log").Range("H1:H50000")
    
    For zPosition = 1 To yLengthOfIndicator
        CurrentProjectIndicator = Mid(xProjectIndicators, zPosition, 1)
        answer = Application.WorksheetFunction. Sum(If(logFirstProjID = ProjectID,If(logProjectIndicator = CurrentProjectIndicator, logHours)))
        LOE = LOE + answer
    Next
        
End Function

Any particular reason you're not using the native SUMIFS function ?
(new to XL2007)

Your present formula would need to be an Array and the SUMIFS function will be far more efficient esp. if used over entire column references.