Problem with IF statement

nhandal

Board Regular
Joined
Apr 18, 2008
Messages
87
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:

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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
Just to point out that you could do this with a SUMPRODUCT Formula...
Code:
=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
 
Last edited:

nhandal

Board Regular
Joined
Apr 18, 2008
Messages
87
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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

Try

Rich (BB 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
                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

Board Regular
Joined
Apr 18, 2008
Messages
87
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:
Code:
=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

Active Member
Joined
Jul 28, 2010
Messages
348

ADVERTISEMENT

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

Code:
[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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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?

Code:
[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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Apr 18, 2008
Messages
87
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,158
Messages
5,657,160
Members
418,363
Latest member
Debating_Earth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top