SumIf using Excel VBA

by

You may need to change the references as commented throughout my code, but this sub should do the trick for you. Also, I used sumif formulas, but if your raw dataset ever grows larger, I’d suggest to look into using collections instead as an efficiency measure.

Option Explicit

Sub SumByPK()

Dim dt As Object
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim sh As Worksheet
Dim InputRng As Range
Dim OutRng As Range
Dim rng As Range
Dim qty, JanPrice, FebPrice As Double
Set dt = CreateObject("Scripting.Dictionary")
Set sh = Sheet1 'sheet object, change this if different

lastrow1 = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row 'determines last row, assuming there are no other data in A column but the primary keys.

Set InputRng = sh.Range("A2:A" & lastrow1) 'change the range ref in different
Set OutRng = sh.Range("G2") 'will output unique primary keys onto this range, change this if needed.
For Each rng In InputRng
    If rng.Value <> "" Then
    dt(rng.Value) = ""
    End If
Next rng

OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)

lastrow2 = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row 'determines last row, assuming there are no other date in G column but the unique primary keys.

For Each rng In sh.Range("G2:G" & lastrow2) 'change the range reference if the OutRng has been changed.
    If rng.Value <> "" Then
        qty = Application.WorksheetFunction.SumIf(sh.Range("A2:A" & lastrow1), rng.Value, sh.Range(Cells(2, 2).Address & ":" & Cells(lastrow1, 2).Address))
        JanPrice = Application.WorksheetFunction.SumIf(sh.Range("A2:A" & lastrow1), rng.Value, sh.Range(Cells(2, 3).Address & ":" & Cells(lastrow1, 3).Address))
        FebPrice = Application.WorksheetFunction.SumIf(sh.Range("A2:A" & lastrow1), rng.Value, sh.Range(Cells(2, 4).Address & ":" & Cells(lastrow1, 4).Address))
        rng.Offset(0, 1).Value = qty
        rng.Offset(0, 2).Value = JanPrice
        rng.Offset(0, 3).Value = FebPrice
    End If
Next rng

End Sub�
�

More Related Contents:

  1. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  2. I am confused on purpose of using “Not”, also on matching of “If” criteria, code says “is nothing” else run code for “MsgBox”
  3. How do I put double quotes in a string in vba?
  4. Excel VBA – How to add dynamic array formula
  5. How can I URL encode a string in Excel VBA?
  6. Check if the file exists using VBA
  7. Compare double in VBA precision problem
  8. Row count on the Filtered data
  9. Excel VBA Macro: User Defined Type Not Defined
  10. Automate IE via Excel to fill in a dropdown and continue
  11. Run-Time error 1004 Excel 2013
  12. VBA – Write all possible combinations of 4 columns of data [duplicate]
  13. Excel VBA to Export Selected Sheets to PDF
  14. Remove duplicates from array using VBA
  15. Controlling Internet Explorer local intranet using Excel VBA
  16. 2 ways for “ClearContents” on VBA Excel, but 1 work fine. Why? [duplicate]
  17. How to automatically insert a blank row after a group of data
  18. Count unique values in Excel
  19. Get File Path (ends with folder)
  20. Get length of array?
  21. Email a single attachment from folder of files each to a different person
  22. Bad data selection using VBScript , on date field values
  23. Excel error 1004 “Unable to get …. property of WorksheetFunction class” appearing inconsistently
  24. Sending object array to variant works before August 2019 Windows update, but fails after update
  25. Find last row in range
  26. How can I install/use “Scripting.FileSystemObject” in Excel 2010 for MAC?
  27. Differences between Excel’s Form Controls & ActiveX Controls
  28. Excel VBA User Defined Function that counts cells with conditional formatting
  29. Get Timezone Information in VBA (Excel)
  30. Excel is calculating a formula with a VBA function as an error unless it is re-entered

Leave a Comment