Consulting

Results 1 to 4 of 4

Thread: Conditional sum with DATE in VBA

  1. #1

    Lightbulb Conditional sum with DATE in VBA

    Good afternoon,
    The following is not an easy question but, I hope, something can help me with that.

    Basically what I want to do is a conditional sum (SUMIFS) in Vba.

    I would like to sum certain values contained in a worksheet of mine (VBS is its name). IF and only IF the corresponding initial and final DATE (such values are associated to an event with an INITIAL date and an END date) are between a certain date range (monthly).

    Obviously I also need to loop over the rows of VBS to "catch" all the values.

    Let us say that an event which value is 500$ starts 02/04/2020 and ends 04/07/2020: in this case I would like to pick the value (500$) associated to the event and use it to populate the second, third and 4th position of my 1D array "arr_date" -> arr_date (1) = arr_date (2)=arr_date(3)= 500.

    Does anyone have an idea about how to cope with the problem?

    Thank you to all.


  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Please attach a workbook to experiment on.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Let us say that an event which value is 500$ starts 02/04/2020 and ends 04/07/2020: in this case I would like to pick the value (500$) associated to the event and use it to populate the second, third and 4th position of my 1D array "arr_date" -> arr_date (1) = arr_date (2)=arr_date(3)= 500.
    Also, since I find the rules a little fuzzy, please include some examples of the expected results

    I'm not understanding 1) the array, and 2) the need for SUMIFS

    I'm guessing your array is 0-based, but you're just using 1-3??
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4

    Post

    Public Sub TEST(Optional silent As Boolean = False)
    
    [... ]
        
         j = 0
        For i = 1 To UBound(mat_Rischi)
    ErrorHandler_RepeatCycle1:
            j = j + 1
            If mat_RBS(j, 17) = "Active" Then
                mat_Rischi(i, 4) = mat_RBS(j, 12) ' Event
                mat_Rischi(i, 5) = mat_RBS(j, 22) ' Exposure Start Date
                mat_Rischi(i, 6) = mat_RBS(j, 23) ' Exposure Finish Date
                mat_Rischi(i, 7) = mat_RBS(j, 34) ' Risk Expected Value (Gross)- EUR -
    
    
             
                
        
          Else
                GoTo ErrorHandler_RepeatCycle1
            End If
        Next i
        
       
        
       WS_R_GA.Range(WS_R_GA.Cells(InitRow, 1), WS_R_GA.Cells(LastRow, LastColumn)).Value = mat_Rischi
        
        'I need to define 3 array: "Sum_Total (60)" where to store the sum of Gross Risk Exp. for each month
        'and arr_dateSD(60,1) together with arr_dateED, containing START DATE and END DATE of each month in period 2020-2024, with which to compare SD and ED of each EVENT
        
        'Define the matrix "Sum_Total" to paste on the sheet
        
        
        
        Dim arr_dateSD As Variant
        ReDim arr_dateSD(0 To 59)
        
        For i = 0 To UBound(arr_dateSD)
            arr_dateSD(i) = DateAdd("m", i, WS_R_C.Range("G8"))
            
            Next i
            
        'How to avoid to start the lop from j=0? Use "DateAdd" not from G8 but from one month before!
        
    '    arr_dateSD_Support = DateAdd("m", -1, WS_R_C.Range("G8"))
    '    arr_dateSD(i) = DateAdd("m", i, arr_dateSD_Support)
            
        
        Dim arr_dateED As Variant
        ReDim arr_dateED(1 To 60)
        
           For i = 1 To UBound(arr_dateED)
            arr_dateED(i) = DateAdd("m", i, WS_R_C.Range("G8"))
            
            Next i
        
        Dim Sum_Total() As Variant
        ReDim Sum_Total(1 To 60)
        
        'loooop
      ' For Z = 1 To UBound(arr_dateED)
        j = 0
            For i = 1 To UBound(mat_Rischi)
                 For j = 1 To UBound(Sum_Total)
                 'If arr_dateSD(j - 1) < mat_Rischi(i, 5) < arr_dateED(j) Then
    '            If mat_Rischi(i, 6) > arr_dateED(j) And mat_Rischi(i, 5) > arr_dateSD(j - 1) Then
    
    
                If mat_Rischi(i, 5) < arr_dateED(j) < mat_Rischi(i, 6) Then
                 Sum_Total(j) = Sum_Total(j) + mat_Rischi(i, 7)
               
               End If
                Next j
                
                Next i

    Above you can see part of my code.

    My question is: say that a project with a risk expected value of 500 starts 20.02 and ends 20.05, how can I assign such "500" to the MONTH 2,3,4,5 (with start date 15.02, 15.03, 15.04, 15.05 and end date 15.03, 15.04, 15.05, 15.06 respectively)???

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •