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.

 

[Special-K99]

No VBA expert but don't you need an

END IF

for each

IF

in VBA?

You have 3 IFs and only one END IF

Maybe you could change that IF...THEN IF... THEN IF...
to

IF...AND...AND...AND

?
Then you'd only need one END IF

 

[AlphaFrog]

Just to point out that you could do this with a SUMPRODUCT Formula...

=SUMPRODUCT((Projects_List!C2:C1500=A1)*(Projects_List!I2:I1500=B1)*(Projects_List!G2:G1500=C1)*(Projects_List!E2:E1500))

In this example, cells A1, B1 and C1 have your lookup criteria values.

If you have Excel 2007 or later, you could use the SUMIFS function instead of SUMPRODUCT

 

[nhandal]

Thank you Special K-99 and Alphafrog

For the if statements, I also tried to have three End If, it still does not work, as for the AND, I thought about it but could not figure out how the code would be.

As for the SUMPRODUCT function, I am using Excel 2003 and tried the SUMIF and SUMPRODUCT before I thought of a VBA function. I tried different configurations that I found on the internet, unfortunatly none worked, also I tried your version and did not work too, it gives #VALUE!. I think this is because the SUMPRODUCT function sums the producst of two ranges and in this case I only need to sum the amounts in one range in case the criteria matches in the three other ranges.

 

[Jonmo1]

You need to close off all 3 ifs, only 1 if has the End If..

Try

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
                End If
            End If
        End If
    Next i
    RegionSum = Total
 
End Function

Also, sumproduct will work.
Did you enter it EXACTLY as Alpha posted it?
If you did any modifcations to it to adjust for ranges, that's fine..
But post the EXACT formula you tried..

 

[nhandal]

jonmo1,

Thanks, I pasted your code but still don't work, I get #VALUE!.

I tried Alpah's function but adjusted it for the cells where the criteria is located on the Summary-Sheet, as follows:

=SUMPRODUCT(('Projects_ List'!C3:C634=Summary_Sheet!C5)*('Projects_ List'!I3:I634=Summary_Sheet!D6)*('Projects_ List'!G3:G634=Summary_Sheet!E2)*('Projects_ List'!E3:E634))

But it also did not work.

 

[prabby25101981]

Not too sure, but shouldn't there be quotes around the values which are being checked in if?

[COLOR=black]If [/COLOR]Sheets("Projects_List").Cells(i, 3).Text = [COLOR=red]"[/COLOR]Region[COLOR=red]"[/COLOR] Then
[COLOR=black]  If[/COLOR] Sheets("Projects_List").Cells(i, 9).Text = [COLOR=red]"[/COLOR]ProjectType[COLOR=red]"[/COLOR] Then
[COLOR=black]    If[/COLOR] Sheets("Projects_List").Cells(i, 7).Text = [COLOR=red]"[/COLOR]ProjectStatus[COLOR=red]"[/COLOR] Then
      Total = Total + Sheets("Projects_List").Cells(i, 5).Value
[COLOR=black]    End If[/COLOR]
[COLOR=black]  End If[/COLOR]
[COLOR=black]End If[/COLOR]

 

[Jonmo1]

Those are variables defined in the code.
Function RegionSum(Region As String, ProjectType As String, ProjectStatus As String) As Integer

Not too sure, but shouldn't there be quotes around the values which are being checked in if?

[COLOR=black]If [/COLOR]Sheets("Projects_List").Cells(i, 3).Text = [COLOR=red]"[/COLOR]Region[COLOR=red]"[/COLOR] Then
[COLOR=black]If[/COLOR] Sheets("Projects_List").Cells(i, 9).Text = [COLOR=red]"[/COLOR]ProjectType[COLOR=red]"[/COLOR] Then
[COLOR=black]  If[/COLOR] Sheets("Projects_List").Cells(i, 7).Text = [COLOR=red]"[/COLOR]ProjectStatus[COLOR=red]"[/COLOR] Then
      Total = Total + Sheets("Projects_List").Cells(i, 5).Value
[COLOR=black]  End If[/COLOR]
[COLOR=black]End If[/COLOR]
[COLOR=black]End If[/COLOR]

 

[Jonmo1]

Do any of the values in 'Projects_ List'!E3:E634 have a formula resulting in an Errror?

I believe that would cause the overall formula to error, in both the VBA and Sumproduct.


Actually, any error within ANY of the ranges referred to in the formula and/or vba code would cause the error.

 

[nhandal]

Ok, there was one cell with a character in it, I cleared the cell and the SUMPRODUCT function worked and that is very helpful. That was a good catch, thanks.

The VBA function is still not working, and I really appreciate if someone can figure out what the problem is with VBA code since I need to build more IF functions with string variables in this sheet.