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:
Thank you in advance.
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:
Code:
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.