PDA

View Full Version : Solved: Sum particular data

Klartigue
08-07-2012, 01:15 PM
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
08-07-2012, 02:33 PM
Try this

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

Klartigue
08-08-2012, 08:18 AM
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??

Klartigue
08-08-2012, 10:39 AM
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
08-08-2012, 10:41 AM
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

08-08-2012, 11:12 AM
ActiveCell.Formula = "=SUM(\$I\$2:\$I\$" & lastrow & ")"

Bob Phillips
08-08-2012, 12:40 PM
Never select

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

Klartigue
08-08-2012, 01:02 PM
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

08-08-2012, 01:04 PM
try
If InStr(.Cells(i, "C").Text,"MERRILL") > 0 Then

Klartigue
08-08-2012, 01:09 PM
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
08-08-2012, 01:15 PM
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

08-08-2012, 01:25 PM
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
08-08-2012, 01:38 PM
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

08-08-2012, 01:44 PM
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
08-08-2012, 01:56 PM
Great, it does work now. Thank you!!