Consulting

Results 1 to 16 of 16

Thread: Solved: Sum particular data

  1. #1
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location

    Solved: Sum particular data

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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Try this

    =SUMPRODUCT(--($C$2:$C$6=LEFT($H9,FIND(" ",$H9&" ")-1)),$I$2:$I$6)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    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:

    [VBA]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[/VBA]


    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!

  4. #4
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    Maybe something like this to sum the merrill quantities? This doesnt work but perhaps it is a step in the right direction?
    [VBA]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[/VBA]

  5. #5
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    This places the sum in the right place but it does not generate a sum. How do I say to I2 to lastrow?

    [VBA]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
    [/VBA]

  6. #6
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    [VBA]ActiveCell.Formula = "=SUM($I$2:$I$" & lastrow & ")"[/VBA]
    ------------------------------------------------
    Happy Coding my friends

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Never select

    [VBA] .Cells(lastrow + 3, 9).Formula = "=SUM($I$2:I" & lastrow & ")"
    [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    Thats works great! But it seems like this part inst working for me:

    [VBA]If .Cells(i, "C").Value = "MERRILL" Then
    [/VBA]

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

  9. #9
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    try
    [VBA]If InStr(.Cells(i, "C").Text,"MERRILL") > 0 Then[/VBA]
    ------------------------------------------------
    Happy Coding my friends

  10. #10
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location

    sum particular cells

    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.

    [VBA]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[/VBA]
    Attached Files Attached Files

  11. #11
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    I think it isn't working becasue this line is messing it up

    [VBA]With .Cells(lastrow + 3, 9).Select
    [/VBA]

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

  12. #12
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    [VBA]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[/VBA]
    ------------------------------------------------
    Happy Coding my friends

  13. #13
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    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

  14. #14
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    i just tested this on your spreadsheet and it works:
    [VBA]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[/VBA]
    ------------------------------------------------
    Happy Coding my friends

  15. #15
    VBAX Mentor
    Joined
    Aug 2011
    Posts
    353
    Location
    Great, it does work now. Thank you!!

  16. #16
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    mark the thread solved
    ------------------------------------------------
    Happy Coding my friends

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •