Solved: Sum particular cells

[Lartk]

See the attached spreadsheet. I am still not having success with summing particular cells. On the "Current" sheet, the Quantity in column AC is the block quantity for each trade. As you can see, each trade is divided up. So one trade is the Buy (B located in column H) or symbol AAAAAA (located in Column G) and divided up by accounts (quantity in colum AN.) For each trade, for the block quantity, I would like to sum the quantities in column AN for that trade. So for example, for the B of AAAAAA, sum AN9-AN11 and place that value in AC9-AC11.

For the rows A13-A15, trade of S (in column H) symbol AAAAAA (in column G) I would like to sum AN13-AN15 and place that cells AC13-AC15. Etcc.

The rows always differ since there can be many variations of trades so it needs to be a general formula.

Hope you can help.

[Bob Phillips]

Try this

=IF(G9="","",SUMPRODUCT(--($G$9:$G$1000=$G9),--($H$9:$H$1000=$H9),$AN$9:$AN$1000))

[Lartk]

I tried this code but an getting an application defined or object defined error?..

[VBA]
Sub BlockQuantityfidelity()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 9 To lastrow
ActiveCell.FormulaR1C1 = "=IF(G9="","",SUMPRODUCT(--($G$9:$G$1000=$G9),--($H$9:$H$1000=$H9),$AN$9:$AN$1000))"
Next i
End With
End Sub
[/VBA]

[Bob Phillips]

You need to double-up on the quotes

[VBA]Sub BlockQuantityfidelity()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 9 To lastrow
ActiveCell.FormulaR1C1 = "=IF(G9="""","""",SUMPRODUCT(--($G$9:$G$1000=$G9),--($H$9:$H$1000=$H9),$AN$9:$AN$1000))"
Next i
End With
End Sub [/VBA]

[Lartk]

[VBA]Sub BlockQuantityfidelity()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 9 To lastrow
ActiveCell.FormulaR1C1 = "=IF(G9="""","""",SUMPRODUCT(--($G$9:$G$1000=$G9),--($H$9:$H$1000=$H9),$AN$9:$AN$1000))"
Next i
End With
End Sub
[/VBA]

I tried the above and still get the same error.

[Bob Phillips]

Missed a couple of points.

Why do you have a loop, but refer to the Activecell?

If should be Formula not FormulaR1C1.

[Lartk]

I fixed the code, but now realized that it is not working how it is supposed to. If you run the below two codes on the attached sheet, you can see that column AC for each trade does not sum the quantities in AN for each trade. For example, for the Sale of AAAAAAA (S in column H represents it is a sell and symbol in column G), the amount in AC should be 250000 bc that is the sum of all the quantities for that trade in column AN.

For example, the Buy of FFFFFFFF (B in column H represents a buy, symbol in column H) the values in AC19-AC21 should be 40000 becasue those are the sum of all the allocations for that trade (allocation quantites located in column AN).

See Sheet 3 to see the correct results of how everything should turn out.

[VBA] Sub BlockQuantityfidelity()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 9 To lastrow

With .Cells(i, "AC").Select
ActiveCell.Formula = "=IF(G9="""","""",SUMPRODUCT(--($G$9:$G$1000=$G9),--($H$9:$H$1000=$H9),$AN$9:$AN$1000))"


End With

Next i
End With
End Sub
Sub DeleteExtra()

Dim lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 9 To lastrow

If Cells(i, "A").Value2 = "" Then

.Rows(i).Clear
End If
Next i
End With

Application.ScreenUpdating = True

End Sub[/VBA]

[Lartk]

The code works for the very first trade on the sheet and carries that sum down for all the other trades, which is incorrect.

[Bob Phillips]

[VBA]Sub BlockQuantityfidelity()
Const FORMULA_SUM As String = _
"=IF($G9="""","""",SUMPRODUCT(--($G$9:$G$<lastrow>=$G9),--($H$9:$H$<lastrow>=$H9),$AN$9:$AN$<lastrow>))"
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("AC9").Resize(lastrow - 8).Formula = Replace(FORMULA_SUM, "<lastrow>", lastrow)
End With
End Sub[/VBA]

[Lartk]

That works, thank you!