0

enter image description here How can I sum all in the Column "I" for each filter in Column "Q" in Sheet1? For example, referring on the sample sheet above; Sum all the column I for 27744 only in Column "Q". Sum all the column I for 27745 only in Column "Q"... and so on..

Then the sum will reflect on Sheet2, like below: enter image description here

The Column "Q" entries in Sheet1 are always different, the above is only an example. Found this link but I don't know if it can b used in this case.

I hope my question is clear. Thank you in advance for advice/help.

Community
  • 1
  • 1
Paolo Medina
  • 303
  • 4
  • 15
  • What have you tried? What is wrong with what you have tried? I would suggest either the Data/Subtotal wizard, or a Pivot Table.. – Ron Rosenfeld Apr 07 '16 at 10:30
  • I updated the question to make it more clear. I was also following [this discussion](http://stackoverflow.com/questions/17835292/get-the-sum-of-visible-rows-in-vba), will do some experiment and will let you know – Paolo Medina Apr 07 '16 at 20:28
  • Just use a Pivot Table, as I suggested – Ron Rosenfeld Apr 07 '16 at 21:27
  • That's a good idea actually. I'll just record a macro while doing the pivot. Let's how it will turn out. Thank you! – Paolo Medina Apr 07 '16 at 21:59

1 Answers1

0

Would be nicer with cells and some tidying up but this should work - 

Sub test()

    Dim dictSums As Scripting.Dictionary
    Set dictSums = New Scripting.Dictionary

    Dim rngTarget As Range
    Set rngTarget = Sheet1.Range("Q1")

    Do While rngTarget.Value <> ""
        With rngTarget
            If Not dictSums.Exists(.Value) Then
                dictSums.Add .Value, .Offset(0, -8).Value
            Else
                dictSums(.Value) = dictSums(.Value) + .Offset(0, -8).Value
            End If
        End With
        Set rngTarget = rngTarget.Offset(1, 0)
    Loop

    Dim varKey As Variant

    For Each varKey In dictSums.keys
        Debug.Print , varKey, dictSums(varKey)
    Next

End Sub
  • I am having a compile error after trying the above. User-defined type not defined. Will try to experiment more using your suggested script. Thank you! – Paolo Medina Apr 07 '16 at 20:36
  • Go to Tools -> References -> scroll down the list and check Microsoft Scripting Runtime -> Ok –  Apr 08 '16 at 08:07