Solved: Sum particular data

[Klartigue]

On the attached sheet, how can I say that if cell in column C = "Merrill", then sum the numbers in column I for I2 to lastrow. And place that sum in .Cells(lastrow + 1, 9)??

[Bob Phillips]

Try this

=SUMPRODUCT(--($C$2:$C$6=LEFT($H9,FIND(" ",$H9&" ")-1)),$I$2:$I$6)

[Klartigue]

That works great. However, each sheet will have a different number of rows. So Can I alter this to say instead of $C$2:$C$6 to say $C2: lastrow?

Also, "MERRILL LYNCH" will not always be located in cell $H9. It will always be found in this location:

Sub Merrill()
Dim lastrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row + 2
.Cells(lastrow + 1, 8).Value = "MERRILL LYNCH"

End With
End Sub


And since there will be a different number of rows each time, I would need to sum $I2:lastrow for those in which column C has "Merrill".

So can we make this a little more generic to reference last rows, etcc..So it will work for many different sheets with different # of rows??

Thanks, this is very helpful!

[Klartigue]

Maybe something like this to sum the merrill quantities? This doesnt work but perhaps it is a step in the right direction?
Sub testingout()
'

Dim lastrow As Long
Dim i As Long
With ActiveSheet

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


If .Cells(i, "C").Value = "MERRILL" Then

.Cells(lastrow + 2, 9).Select
ActiveCell.FormulaR1C1 = "=sum(I2:I)"
End If
End With
End Sub

[Klartigue]

This places the sum in the right place but it does not generate a sum. How do I say to I2 to lastrow?

Sub testingout()
'

Dim lastrow As Long
Dim i As Long
With ActiveSheet

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


If .Cells(i, "C").Value = "MERRILL" Then

.Cells(lastrow + 3, 9).Select
ActiveCell.FormulaR1C1 = "=sum($I$2:I)"

End If
Next i
End With

End Sub

[CatDaddy]

ActiveCell.Formula = "=SUM($I$2:$I$" & lastrow & ")"

[Bob Phillips]

Never select

.Cells(lastrow + 3, 9).Formula = "=SUM($I$2:I" & lastrow & ")"

[Klartigue]

Thats works great! But it seems like this part inst working for me:

If .Cells(i, "C").Value = "MERRILL" Then


I am trying to sum the cells in column I in which they have "Merrill" in Column C

[CatDaddy]

try
If InStr(.Cells(i, "C").Text,"MERRILL") > 0 Then

[Klartigue]

See the attached. If you try the code it is still calculating the sum of everything in colum I regardless of what is says in column C.

Sub testingout()
'

Dim lastrow As Long
Dim i As Long
With ActiveSheet

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


If InStr(.Cells(i, "C").Text, "MERRILL") > 0 Then

With .Cells(lastrow + 3, 9).Select
ActiveCell.Formula = "=SUM($I$2:$I$" & lastrow & ")"

End With
End If

Next i
End With

End Sub

[Klartigue]

I think it isn't working becasue this line is messing it up

With .Cells(lastrow + 3, 9).Select


But somehow i still need to reference that cell to place the sum in

[CatDaddy]

Sub testingout()
'

Dim lastrow As Long
Dim i As Long
With ActiveSheet

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


If InStr(.Cells(i, "C").Text, "MERRILL") > 0 Then

.Cells(lastrow + 3, 9).Value = .Cells(lastrow + 3, 9).Value + .Cells(i, "I").Value

End With
End If

Next i
End With

End Sub

[Klartigue]

Thats not working bc it is not suming the correct cells. In the attached excel sheet my ultimate goal is to sum I2 to I5 because those correspond to MERRILL (in Column C). And that sum to be placed in .Cells(lastrow + 3, 9).Value

[CatDaddy]

i just tested this on your spreadsheet and it works:
Sub testingout()

Dim lastrow As Long
Dim i As Long
With ActiveSheet

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

If InStr(.Cells(i, "C").Text, "MERRILL") > 0 Then

.Cells(lastrow + 3, 9).Value = .Cells(lastrow + 3, 9).Value + .Cells(i, "I").Value
End If

Next i
End With

End Sub

[Klartigue]

Great, it does work now. Thank you!!

[CatDaddy]

mark the thread solved :)