Consulting

Results 1 to 10 of 10

Thread: Sum if

  1. #1
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location

    Sum if

    I have the attached document and on Sheet 1 is a breakdown of each different trade we did. Each blank row divides the different trades. Originally, some trades may have several pieces going to the same custodian. Is there a general function to write that says for each trade, if the custodian in column G is the same, then sum the quantities in column I and place that quantity in the in column J (and in the first row in which the custodian name first appears).

    Sheet 2 has the desired results.

    I can't figure out either a sum if function or some other way to have it sum the quantities for each custodian, and be able to differentiate between each block trade.

    Let me know.

    Thanks
    Attached Files Attached Files

  2. #2
    I don't know how to create a function for your situation but I can try another way
    Try this code
    Sub SumBasedOnCustodian()
        Dim LR As Long
        Dim I As Long
        LR = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
        Application.ScreenUpdating = False
            Range("J2:J" & LR).ClearContents
            For I = 2 To LR
                If Not IsEmpty(Cells(I, 10).Offset(, -3)) And Cells(I, 10).Offset(-1, -3) <> Cells(I, 10).Offset(, -3) Then
                    If Cells(I, 10).Offset(, -3) <> Cells(I, 10).Offset(1, -3) Then
                        Cells(I, 10).Value = Cells(I, 10).Offset(, -1).Value
                    ElseIf Cells(I, 10).Offset(, -3) = Cells(I, 10).Offset(1, -3) And Cells(I, 10).Offset(1, -3) <> Cells(I, 10).Offset(2, -3) Then
                        Cells(I, 10).Value = "OK"
                        Cells(I, 10).Value = Cells(I, 10).Offset(, -1).Value + Cells(I, 10).Offset(1, -1)
                    Else
                        Cells(I, 10).FormulaArray = "=SUM(OFFSET(RC[-1],0,0,MATCH(1,IF(RC[-1]:R[37]C[-1]="""",1,0),0),1))"
                        Cells(I, 10).Value = Cells(I, 10).Value
                    End If
                End If
            Next I
        Application.ScreenUpdating = True
    End Sub

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
        With Sheet1.UsedRange.Resize(, 12)
          .AutoFilter 7, "Merrill lynch"
          For Each ar In .Columns(7).Offset(1).SpecialCells(12).Areas
            ar.Cells(1).Offset(, 5) = Application.Sum(ar.Offset(, 2))
          Next
          .AutoFilter
        End With
    End Sub

  4. #4
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location

    Sum if thread

    Yasserkhalil- the code you gave is close but see the attached, particularly what is highlighted in yellow. When there is a situation where the custodians are not sorted to go in order and they skip around within each block trade, this code does not work. For each block trade it needs to sum all the Fidelity and place in column J of the first Fidelity line, and etc.. for the other custodians.
    Attached Files Attached Files

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      With Sheet1.UsedRange
        .Columns(7).AdvancedFilter 2, , Sheet1.Cells(1, 20), True
        sn = .Columns(20).Offset(1).SpecialCells(2)
        
        With .Resize(, 12)
          For Each it In sn
            .AutoFilter 7, it
            For Each ar In .Columns(7).Offset(1).SpecialCells(12).Areas
              ar.Cells(1).Offset(, 3) = Application.Sum(ar.Offset(, 2))
            Next
            .AutoFilter
          Next
        End With
      End With
    End Sub

  6. #6
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    When I try the above code It still doesn't regonize all the Fidelity and sum them up. See below results that show my issue. There should be one sum by the first Fidelity entry for 425000. There should not be one Fidelity for 250000 and another Fidelity for 175000. This code has issues when the custodians are not sorted.

    AAA Avalon BUY 1/7/2015 1/9/2015 77777777 100 500000
    AAA Avalon BUY 1/7/2015 1/9/2015 77777777 100 FIDELITY 250000 250000
    AAA Avalon BUY 1/7/2015 1/9/2015 77777777 100 MERRILL LYNCH 75000 75000
    AAA Avalon BUY 1/7/2015 1/9/2015 77777777 100 FIDELITY 135000 175000
    AAA Avalon BUY 1/7/2015 1/9/2015 77777777 100 FIDELITY 40000

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    You are introducing new requirements.
    Add a sorting routine for each area.

  8. #8
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    I was able to use a sort code to sort by custodian first and then the above code worked. Thanks for the help!
    FYI - this came from another thread i started to sort data..thanks Paul Hossler for the code help!

    Option ExplicitSub SortByColG()
        Dim rBroker As Range, rArea As Range, rSort As Range
        
        
        Set rBroker = ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants, 23)
        
        For Each rArea In rBroker.Areas
            Set rSort = rArea.CurrentRegion
            
            'adjust for header row and custodian row
            If rSort.Rows(1).Row = 1 Then
                'header row + total row + single data row = 3 rows so no need to sort
                If rSort.Rows.Count <= 3 Then GoTo NextArea
                Set rSort = rSort.Cells(3, 1).Resize(rSort.Rows.Count - 2, rSort.Columns.Count)
            Else
                'NO header row + total row + single data row = 2 rows so no need to sort
                If rSort.Rows.Count <= 2 Then GoTo NextArea
                Set rSort = rSort.Cells(2, 1).Resize(rSort.Rows.Count - 1, rSort.Columns.Count)
            
            End If
            
            With ActiveSheet.Sort
                .SortFields.Clear
                .SortFields.Add Key:=rSort.Columns(7), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SetRange rSort
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
    NextArea:
        Next
        
        Range("A1").Select
         End Sub
    Last edited by Klartigue; 01-08-2015 at 12:35 PM.

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    You'd better indicate the credits for that code. !!

  10. #10
    Mr. Klartigue
    Try this attachment
    Attached Files Attached Files

Posting Permissions

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