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)??

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)

=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??

Thanks, this is very helpful!

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

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

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

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

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 & ")"

.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

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

08-08-2012, 01:04 PM

try

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

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

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

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

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

CatDaddy

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

'

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

CatDaddy

08-08-2012, 01:44 PM

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

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!!

CatDaddy

08-08-2012, 02:04 PM

mark the thread solved :)

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.