# Problem with IF statement

#### nhandal

##### Board Regular
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``````

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Special-K99

##### Well-known Member
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
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
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
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
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
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
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
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
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.

1,106,001
Messages
5,508,713
Members
408,690
Latest member
Lip Renan

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...