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.
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,343
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,271
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
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
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top