Guides

Group transactions and statistics

Aggregate one value column by a key, then combine aligned summaries.

Calculate totals by key#

GroupBy accepts a rectangular 2D array and returns a 1-based two-column array: key, aggregate. Groups appear in first-seen order. The original array is not modified.

SEE THE TRANSFORMATIONGroup, calculate sum / mean / count, then append

Before

records2D · 4 × 2

Rows 0..3 · Columns 0..1

row / col	0	1
0	“North”	120
1	“South”	80
2	“North”	200
3	“South”	40

After

report2D · 2 × 4

Rows 1..2 · Columns 1..4

row / col	1	2	3	4
1	“North”	320	160	2
2	“South”	120	60	2

Report columns: key, sum, mean, count. Each GroupBy first produces a 2 × 2 matrix; appending the two statistic columns produces 2 × 4.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Sub SummarizeTransactions()
    Const GROUP_SUM As Long = 1   ' GroupType.eSum
    Const GROUP_MEAN As Long = 2  ' GroupType.eMean
    Const GROUP_COUNT As Long = 3 ' GroupType.eCount
    Const SELECT_COLUMN As Long = 1 ' SelectionType.eSelectColumn
    Dim records As Variant, totals As Variant
    Dim means As Variant, counts As Variant, report As Variant
    records = Arr.DimMerge(VBA.Array( _
        VBA.Array("North", 120), VBA.Array("South", 80), _
        VBA.Array("North", 200), VBA.Array("South", 40)))
    totals = Arr.GroupBy(records, 0, 1, GROUP_SUM)
    means = Arr.GroupBy(records, 0, 1, GROUP_MEAN)
    counts = Arr.GroupBy(records, 0, 1, GROUP_COUNT)
    If Not IsArray(totals) Then Exit Sub
    If Not IsArray(means) Then Exit Sub
    If Not IsArray(counts) Then Exit Sub
    ' All three calls use the same records and first-seen key order.
    report = Arr.InsertColumns(totals, _
        Arr.SliceArray(means, SELECT_COLUMN, 2))
    report = Arr.InsertColumns(report, _
        Arr.SliceArray(counts, SELECT_COLUMN, 2))
    Debug.Print report(1, 1), report(1, 2), report(1, 3), report(1, 4)
    ' Expected: North, 320, 160, 2
End Sub

What each aggregate counts#

eCount counts non-Empty, non-Null, non-error values whose string representation has length greater than zero. It includes nonnumeric text and is not a row count. The other modes use numeric values, including numeric text, and skip Empty, Null, errors, and nonnumeric text. Groups with no eligible values still appear, with an aggregate of zero.

eStDev uses Welford’s update and divides by the numeric count, giving a population standard deviation. A singleton group returns zero. Use Finance.StDev for an ungrouped column with the same population denominator.

Grouping keys#

The dictionary uses binary comparison, so text key case matters. Empty keys form one group. A Null or Excel-error key aborts the whole function and returns Empty; it does not skip that row. Dictionary key equality is not the case-insensitive numeric/text matching used by Filter. Object and array keys are not supported as a general key format.

Calculate an ungrouped statistic#

Sub ColumnStatistics()
    Const SELECT_COLUMN As Long = 1 ' SelectionType.eSelectColumn
    Dim records As Variant, amounts As Variant
    records = Arr.DimMerge(VBA.Array( _
        VBA.Array("North", 120), VBA.Array("South", 80)))
    amounts = Arr.SliceArray(records, SELECT_COLUMN, 1)
    Debug.Print Fin.Sum(amounts)  ' Expected: 200
    Debug.Print Fin.Mean(amounts) ' Expected: 100
End Sub

Finance reads only the first column of a 2D array. Slice the intended measure column explicitly; passing a full transaction table does not aggregate every numeric cell.