Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All VBA snippets

Total by key with a Dictionary (group-by in code)

Sum amounts per customer — or build a unique list — with Scripting.Dictionary, the closest VBA gets to a pivot in code.

1What it does

Uses a Scripting.Dictionary to group rows by a key (a customer, account, or category in column A) and total another column (B) as it goes: dict(key) = dict(key) + amount. A key it hasn't seen silently starts at 0, so there's no 'does this key exist yet' bookkeeping. Then it writes the unique keys and their totals back out. The same pattern with no sum gives you a de-duplicated unique list.

2The code

Sub SumByKey()
    Dim ws As Worksheet, lastRow As Long, i As Long
    Dim dict As Object, key As String, k As Variant
    Set ws = ActiveSheet
    Set dict = CreateObject("Scripting.Dictionary")   ' late-bound: no reference needed
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Group column A (e.g. Customer) and total column B (Amount) — a pivot in code.
    For i = 2 To lastRow
        key = ws.Cells(i, "A").Value
        dict(key) = dict(key) + ws.Cells(i, "B").Value   ' a new key starts at 0
    Next i

    ' Write the unique keys and their totals out to columns D and E.
    i = 2
    For Each k In dict.Keys
        ws.Cells(i, "D").Value = k
        ws.Cells(i, "E").Value = dict(k)
        i = i + 1
    Next k
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾

1
Don't see the Developer tab in the ribbon?
You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
- Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
- Mac:Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
2
Paste in the code
Press Alt + F11 to open the Visual Basic editor, then Insert → Moduleand paste the snippet's code into the blank window. Close it with Alt + Q.
3
Run it
Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
4
Keep the macro — save as .xlsm
File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
5
Macros blocked?
Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

Total sales by customer, spend by vendor, hours by project.
Build a unique / de-duplicated list of values.
Aggregate a detail export into a summary without a PivotTable.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Detail rows

Acme	$1,200
Globex	$800
Acme	$300
Initech	$500
Globex	$200

5Pitfalls

Setting a reference to Microsoft Scripting Runtime that isn't on every machine.

Fix: Late-bind with CreateObject("Scripting.Dictionary") so it runs anywhere, no reference to set.

Keys are case- and type-sensitive ('Acme' vs 'acme', 100 vs the text "100").

Fix: Normalize the key first (Trim, UCase, CStr) so near-duplicates collapse together.

6No-code alternatives

SUMIFS / PivotTable — For a one-off summary, a formula or pivot is faster to set up than code.
Collection — Built-in with no reference, but no easy key-exists check or running total — Dictionary wins for aggregation.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →