PDA

View Full Version : [SOLVED] Sum if



Klartigue
01-06-2015, 03:08 PM
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

YasserKhalil
01-07-2015, 03:17 AM
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

snb
01-07-2015, 03:58 AM
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

Klartigue
01-07-2015, 09:11 AM
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.

snb
01-07-2015, 10:55 AM
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

Klartigue
01-07-2015, 02:39 PM
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

snb
01-07-2015, 04:34 PM
You are introducing new requirements.
Add a sorting routine for each area.

Klartigue
01-08-2015, 10:52 AM
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

snb
01-08-2015, 12:26 PM
You'd better indicate the credits for that code. !!

YasserKhalil
01-10-2015, 04:28 AM
Mr. Klartigue
Try this attachment