VBA Dictionary SumIf: How to Group and Sum Data in Excel

VBA & macros · Updated November 18, 2026

To perform a SUMIF-style total in VBA, use a Scripting.Dictionary: make each category a key and add each matching amount to that key’s value. The macro below groups the values in column B by the labels in column A, then writes one total per label; unlike the worksheet SUMIF function, it creates a fixed summary when the macro runs.

What “VBA Dictionary SumIf” means

Excel’s SUMIF is a worksheet function that adds numbers when a corresponding cell meets one condition. A VBA dictionary is a key-and-value collection: each unique key identifies a stored value. Combining the two ideas means reading rows in VBA, using a category such as a department or product as the key, and accumulating the amounts associated with it.

There is no built-in VBA method named “Dictionary SumIf.” The dictionary is a useful programming pattern for doing conditional totals, especially when you want to create summaries for many different categories in one pass. It is not a replacement for every SUMIF use: a worksheet formula recalculates when its inputs change, while a macro’s output generally stays as it was until the macro runs again.

How the dictionary grouping works

For each source row, the code reads a category and an amount. If the category has not appeared before, it adds a dictionary entry with that amount. If the category already exists, it adds the new amount to the stored total. After the final row, it writes the dictionary’s keys and totals to the worksheet.

  • Key: the category used to group rows, such as “West” or “Travel.”
  • Item: the running total stored for that category.
  • Exists: the dictionary check that determines whether to start a total or add to one.

This is useful for multiple categories because the same loop builds all their totals. With a single condition and a modest data set, a SUMIF formula may be simpler. A dictionary is most valuable when VBA already needs to process the data or when the macro needs to produce many grouped results.

Small worksheet example

Suppose A2:A6 contains region names and B2:B6 contains sales: West 120, East 80, West 50, East 40, and West 30. With region names in A2:A6, sales in B2:B6, and the criterion West in E2, enter this formula in F2:

=SUMIF(A2:A6,E2,B2:B6)

The result is 200, because 120 + 50 + 30 = 200. SUMIF is a good choice if you want the total to update automatically when the source values change. The VBA approach below instead builds totals for every region in one run, rather than requiring one formula for each region.

VBA code to group and sum categories

This example assumes the worksheet is named Sheet1, the category labels are in A2:A onward, and the numeric amounts are in B2:B onward. It writes the summary headers and results in columns D and E. Reserve those columns for the output before running the macro; the code does not clear existing cells below its results.

Sub SumByCategory()
    Dim ws As Worksheet
    Dim totals As Object
    Dim lastRow As Long
    Dim r As Long
    Dim outputRow As Long
    Dim category As Variant
    Dim amount As Variant
    Dim normalizedKey As String
    Dim key As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set totals = CreateObject("Scripting.Dictionary")
    totals.CompareMode = vbTextCompare

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = 2 To lastRow
        category = ws.Cells(r, "A").Value
        amount = ws.Cells(r, "B").Value

        If Not IsError(category) Then
            normalizedKey = Trim$(CStr(category))

            If Len(normalizedKey) > 0 Then
                If Not IsError(amount) Then
                    If IsNumeric(amount) Then
                        If totals.Exists(normalizedKey) Then
                            totals(normalizedKey) = _
                                CDbl(totals(normalizedKey)) + CDbl(amount)
                        Else
                            totals.Add normalizedKey, CDbl(amount)
                        End If
                    End If
                End If
            End If
        End If
    Next r

    ws.Range("D1").Value = "Category"
    ws.Range("E1").Value = "Total"
    outputRow = 2

    For Each key In totals.Keys
        ws.Cells(outputRow, "D").Value = key
        ws.Cells(outputRow, "E").Value = totals(key)
        outputRow = outputRow + 1
    Next key
End Sub

The macro ignores blank or whitespace-only categories, error values in either input column, and amounts that VBA does not recognize as numeric. It trims spaces around category labels and uses a text comparison mode, so labels such as “West” and “WEST” are treated as the same category. The first spelling encountered is used as the output key.

How to add and run the macro

  1. Save a copy of the workbook first. VBA macros cannot be undone with Ctrl+Z.
  2. On Excel for Microsoft 365 for Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, use Tools > Macro > Visual Basic Editor; shortcut availability can vary by keyboard settings.
  3. In the editor, choose Insert > Module and paste the code into the module.
  4. Change “Sheet1” if your worksheet has a different tab name, and confirm that the source data and output columns match the assumptions.
  5. Return to Excel, press Alt+F8 on Windows, select SumByCategory, and choose Run. On Mac, use Tools > Macro > Macros, select the procedure, and run it.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro in the file. Excel may require you to allow macros for a trusted workbook. Do not enable macros in an unfamiliar file simply to view its contents.

Important choices and common problems

Text and number categories: The sample converts each category to text. That makes labels convenient to compare, but it can also make a numeric category 1 and a text category “1” share a key. If those represent different groups in your data, use a deliberate type-aware key scheme instead of converting everything to text.

Spaces and capitalization: Trimming removes spaces at the beginning and end, but it does not standardize internal spaces. “North Zone” and “North Zone” may remain separate. vbTextCompare makes matching case-insensitive; use vbBinaryCompare if capitalization should distinguish keys. Set CompareMode before adding any dictionary entries.

Numbers stored as text: The code includes values that VBA recognizes through IsNumeric and converts them with CDbl. Text such as “not available” is skipped. Numeric text may be interpreted according to regional settings, so check source data if decimal or thousands separators are inconsistent.

Last-row detection: The sample finds the last used row in column A. If column A has gaps, later rows are still visited up to the last nonempty cell, but rows below the final category are not. If the category column is not the best indicator of the data boundary, change the detection logic or use a named Excel table.

Output order and old results: Dictionary key order should not be treated as a guaranteed sort order. Sort the output separately if alphabetical or numerical order matters. Also clear any obsolete results left below a shorter new summary, taking care not to delete other worksheet content.

Dictionary versus SUMIF and other options

Choose the method based on how the result should behave and who needs to maintain it:

  • SUMIF: Best for a visible, formula-driven total for one condition. It recalculates as worksheet inputs change.
  • SUMIFS: Better when a total must meet multiple conditions, such as one region and one month. It remains a worksheet formula rather than a VBA procedure.
  • Dictionary in VBA: Useful for generating many group totals during an automated process. The summary is a snapshot until the macro runs again.
  • PivotTable: Useful for interactive summaries that users can rearrange or filter without editing VBA.
  • Power Query: Useful for repeatable data preparation and grouping, particularly when the source must be refreshed and transformed.

A common misconception is that VBA makes every total faster. The dictionary reduces repeated worksheet calculations when many groups are being accumulated, but performance depends on data size, workbook design, and how the macro reads and writes cells. For a straightforward one-category total, a worksheet formula is usually easier to inspect and update.

Excel for Mac and compatibility

The sample uses late binding with CreateObject("Scripting.Dictionary"), avoiding the need to select a reference in Tools > References on Windows. However, Scripting.Dictionary availability differs across platforms, and this Windows-oriented automation should not be assumed to work in every Excel for Mac installation. Mac Excel also does not support Windows COM or ActiveX components in the same way.

If the macro must run on both Windows and Mac, test it on the exact Excel versions and devices that will use it. Where the dictionary object is unavailable, alternatives include a worksheet SUMIF/SUMIFS summary, a PivotTable, or VBA using a supported collection-based design. Excel for the web cannot run VBA macros; open the workbook in desktop Excel to execute this code.

Frequently Asked Questions

Is there a VBA function called Dictionary SumIf?
No. “Dictionary SumIf” describes a coding pattern: a Scripting.Dictionary stores one running total per key. The worksheet function SUMIF is a separate Excel function.

Can a VBA dictionary sum using more than one condition?
Yes. Build a combined key from the required fields, such as region and month, or use nested dictionaries. Make sure combined keys cannot collide; a separator that never occurs in the source values can help. For ordinary worksheet calculations, SUMIFS may be simpler.

Why does CreateObject(“Scripting.Dictionary”) give an error?
Possible causes include running in an environment where the scripting runtime is unavailable, a platform compatibility issue, or a restriction on automation. This example targets desktop Excel on Windows. Check the platform and macro settings, or use a worksheet summary or another compatible approach.

Does the dictionary summary update when source cells change?
No. The macro reads the source values when it runs and writes ordinary cell values. Run it again after the data changes, or use formulas, a PivotTable, or a refreshable query when automatic or user-triggered updating is preferable.

Can the macro keep categories with zero totals?
Yes, if a category has at least one included row whose amount is zero, it is added to the dictionary and appears with a zero total. A category found only on rows with invalid or error amounts is skipped by this sample; adjust the validation rules if such categories should still be listed.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.