Thread: SumIf - SOLVED

Hi,

Thanks in advance to all who help.

I would like to turn the key functionality of my sheet in yellow into a VBA function. In essence I want the program to scan a bunch of dates and pick the correct rent for each month. Then total the rent for the year. The rents change in different month increments. I have accomplished my goal with some formulas but it is not practical for me to use this in actual work. I would like to automate what I have created in a formula AND only show the year end total.

See attachment.

Thanks.

With an assist from Rylo on using resize, here you go.

Function RentSum(Test_Year As Integer, table_array As Range)

    Dim Start_Date As Long
    Dim End_Date As Long
    Dim Temp_Date As Long
    Dim Date_Range As Range
    Dim Total_Rent As Variant
    
    Set Date_Range = table_array.Resize(, 2)
    Start_Date = Application.WorksheetFunction.Min(Date_Range)
    End_Date = Application.WorksheetFunction.Max(Date_Range)
    Temp_Date = Start_Date
    
    Do While Temp_Date < End_Date
        
        If Year(Temp_Date) = Test_Year Then
        
            Total_Rent = Application.WorksheetFunction.VLookup(Temp_Date, table_array, 4, True) + Total_Rent
                    
        End If
        
        Temp_Date = DateAdd("m", 1, Temp_Date)
          
    Loop
    
    RentSum = Total_Rent
    
End Function

Regards,

Tom

OK, here's the new solution.

Function RentSum(Test_Year As Integer, table_array As Range) As Variant


    Dim Start_Date As Long
    Dim End_Date As Long
    Dim Temp_Date As Long
    Dim Min_Date As Long
    Dim Max_Date As Long
    Dim EOP As Long
    Dim Date_Range As Range
    
    Set Date_Range = table_array.Resize(, 2)                                                        'sets the date range for the min and max date calculatons
    Min_Date = Application.WorksheetFunction.Min(Date_Range)                                        'First Start date from Column C
    Max_Date = Application.WorksheetFunction.Max(Date_Range)                                        'Last End Date from Column D
    Start_Date = DateSerial(Test_Year, 1, 1)                                                        'Jan 1st for the Test_Year
    End_Date = DateSerial(Test_Year, 12, 31)                                                        'Dec 31st for the Test_Year
     
    If Start_Date < Min_Date Then
        Temp_Date = DateSerial(Test_Year, Month(Min_Date), 1)                                       'If the Start_Date < Min_Date then the Start Date is changed to the Min_Date with a day of 1
    Else
        Temp_Date = Start_Date                                                                      'Temp_date set to Start_Date
    End If
    
    If End_Date > Max_Date Then End_Date = Max_Date                                                 'If the End_Date > Max_Date then the End_Date is changed to the Max_Date
            
    If Day(Min_Date) > 1 And Year(Min_Date) = Test_Year Then                                        'Test to see if the Min_Date is other than the 1st day of the month and in the test year
                                                                                                    'The following lines calculate rent for partial month
        Days_in_Month = MonthDays(Temp_Date)                                                        'Number or days in the temp_date month
        Rent_Days = Days_in_Month - Day(Min_Date) + 1                                               'Number of rent days in the temp_date month
        Rent_EOM = WorksheetFunction.VLookup(Min_Date, table_array, 4, True)                        'Montly rent for last portion of month
        RentSum = RentSum + Rent_EOM * Rent_Days / Days_in_Month                                    'Monthly partial rent added to existing total rent
        Temp_Date = DateAdd("m", 1, Temp_Date)                                                      'Increse the Temp_Date by 1 month
    
    End If
    
    Do While Temp_Date <= End_Date                                                                  'Executes a loop for all months betweent the Start_Date and the End_Date, inclusive
        
        EOP = WorksheetFunction.VLookup(Temp_Date, table_array, 2, True)                            'End Date from Column B, EOP = End Of Period
    
        If Month(Temp_Date) = Month(EOP) And Year(Temp_Date) = _
            Year(EOP) And DateSerial(Year(Temp_Date), Month(Temp_Date) + 1, 0) <> EOP Then          'tests to see if rent is split in the month
                                                                                                    'The following lines calculate rent for a split month
            Days_in_Month = MonthDays(Temp_Date)                                                    'Number or days in the temp_date month
            Rent_Days_BOM = Day(EOP)                                                                'Number of days for rent in the 1st Portion of the split month
            Rent_BOM = WorksheetFunction.VLookup(Temp_Date, table_array, 4, True)                   'Monthly Rent for the 1st Portion of the split month
            Rent_Days_EOM = Days_in_Month - Rent_Days_BOM                                           'Number of days for rent in the 2nd Portion of the split month
            Rent_EOM = WorksheetFunction.VLookup(Temp_Date + Days_in_Month, table_array, 4, True)   'Monthly Rent for the 2nd Portion of the split month
            RentSum = RentSum + Rent_BOM * Rent_Days_BOM / Days_in_Month + _
              Rent_EOM * Rent_Days_EOM / Days_in_Month                                              'Monthly rent for split month added to existing total rent
                
        Else                                                                                        'Use the following statement if the rent is not split
          
            RentSum = RentSum + WorksheetFunction.VLookup(Temp_Date, table_array, 4, True)          'Montly rent for a full month added to existing total rent
               
        End If
        
        Temp_Date = DateAdd("m", 1, Temp_Date)                                                      'Increse the Temp_Date by 1 month
       
    Loop
    
    If Day(Max_Date) < MonthDays(Max_Date) And Year(Max_Date) = Test_Year Then                      'Test if the day of the End_date in column D is less than the number of days in the month and in the test year
                                                                                                    'The following lines correct the total rent for partial end month
        Days_in_Month = MonthDays(Max_Date)                                                         'Number or days in the Max_Date month
        Non_Rent_Days = Days_in_Month - Day(Max_Date)                                               'Number of non-rent days in the final month
        Rent = WorksheetFunction.VLookup(Max_Date, table_array, 4, True)                            'Monthly rent for the last month
        RentSum = RentSum - Rent * Non_Rent_Days / Days_in_Month                                    'Rentsum corrected by subtracting non-rent days rent from total rent
    
    End If
    
    
    
End Function
Function MonthDays(Tdate As Long) As Integer

MonthDays = Day(DateSerial(Year(Tdate), Month(Tdate) + 1, 0))                                       'Calculates the number of days in a month

End Function

It has the following changes:
1) I added comments throughout so you can see what each line is doing
2) It now calculates a partial rent for the first month of the first year and the last month of the last year if the first table date doesn't start on the 1st day of the month or the last table date doesn't end on the last day of the month, respectively.
3) It now calucaltes an average monthly rent if the rent for that month changes in the middle of the month.

Note: When you use this you will get a different answer than your sample spreadsheet. You need to change the end date in the table to 9/30/2015 (September only has 30 days) and your hand calc uses 30 days for the denominator when it should use 31 (31 days in July).

You will not get accurate results if the rent splits twice in the same month - I assume this does not happen.

Your dates in the date table must be "legal" dates.

I did a lot of testing and it seems to cover all situations you asked for, but I remind you I am a noob, so I recommend you run several scenarios to insure the results are what you want.

Regards,

Tom

If you found my solution worthwhile, please click on the scales icon and give me a ratiing.