Problem with IF statement

[nhandal]

Hi,

I have a sheet for tracking projects implemented in different regions, I am trying to build a function to calculate the total cost of projects based on three criteria:

1) the region in which the project is implemented, this could be Region A, Region B, Region C.
2) The type of project, this could be Type 1, Type 2, Type 3.
3) Project status, this could be On-Going, Completed, Planned.

I will pass these criteria to the function through string variables. The function will then check rows from 2 to 1500 and calculate the total cost of projects.

I have a workbook with two worksheets, the projects are listed in a sheet named Projects_List. The regions are listed in colum C, project types in colum I, project status in colum G, and project cost in column E.

I build the function below but it is not working. I appreciate it if someone can help me find the error in the function:

Function RegionSum(Region As String, ProjectType As String, ProjectStatus As String) As Integer
    Dim Total As Integer
    Dim i As Integer
 
    Total = 0
 
    For i = 2 To 1500 Step 1
        If Sheets("Projects_List").Cells(i, 3).Text = Region Then
            If Sheets("Projects_List").Cells(i, 9).Text = ProjectType Then
                If Sheets("Projects_List").Cells(i, 7).Text = ProjectStatus Then
                    Total = Total + Sheets("Projects_List").Cells(i, 5).Value
                 Else
                      Total = Total
            End If
    Next i
    RegionSum = Total
 
End Function

Thank you in advance.

 

[Jonmo1]

It works for me.

If the sumproduct worked after making a correction in one of the ranges,
The VBA function will not self recalculate.
You will have to F2 - Enter on the cell holding the formula.

Adding this as the first line will improve that functionality.
Application.Volatile = True

Also, capitalization will matter in the VBA function, but it does not matter in the sumproduct.

To account for that, use ucase

If Ucase(Sheets("Projects_List").Cells(i, 3).Text) = Ucase(Region) Then

Do that for all 3 ifs.


But again, if the sumproduct works, no point in the UDF really...why reinvent the wheel.