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

I don't know how to create a function for your situation but I can try another way
Try this code

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

---

Code:

---

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

---

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.

Code:

---

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

---

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

You are introducing new requirements.
Add a sorting routine for each area.

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!

Code:

---

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

---

You'd better indicate the credits for that code. !!

Mr. Klartigue
Try this attachment