iOfficeAI/OfficeCLI · error · ArgumentException

invalid aggregate: '{func}'. Valid: sum, count, countNums, a

Error message

invalid aggregate: '{func}'. Valid: sum, count, countNums, average/avg, max, min, product, stdDev/std, stdDevp/stdp, var/variance, varP

What it means

ParseSubtotal rejects any aggregate token that does not match a recognised function name or alias. The strict check exists because an earlier version silently fell back to 'sum' on unknown tokens, producing wrong numbers on render (Bug #3). The message lists the valid spellings including aliases so the user can correct the typo in place.

Source

Thrown at src/officecli/Core/PivotTableHelper.Parse.cs:551

    {
        return func.ToLowerInvariant() switch
        {
            "sum" => DataConsolidateFunctionValues.Sum,
            "count" => DataConsolidateFunctionValues.Count,
            "countnums" or "countnum" => DataConsolidateFunctionValues.CountNumbers,
            "average" or "avg" => DataConsolidateFunctionValues.Average,
            "max" => DataConsolidateFunctionValues.Maximum,
            "min" => DataConsolidateFunctionValues.Minimum,
            "product" => DataConsolidateFunctionValues.Product,
            "stddev" or "stdev" or "std" => DataConsolidateFunctionValues.StandardDeviation,
            "stddevp" or "stdevp" or "stdp" => DataConsolidateFunctionValues.StandardDeviationP,
            "var" or "variance" => DataConsolidateFunctionValues.Variance,
            "varp" => DataConsolidateFunctionValues.VarianceP,
            // CONSISTENCY(strict-enums): mirror ParseShowDataAs / ParseFieldList —
            // unknown tokens throw at Add/Set time so typos surface immediately
            // instead of silently falling back to sum and producing the wrong
            // numbers on render (Bug #3).
            _ => throw new ArgumentException(
                $"invalid aggregate: '{func}'. Valid: sum, count, countNums, average/avg, " +
                "max, min, product, stdDev/std, stdDevp/stdp, var/variance, varP"),
        };
    }

    /// <summary>
    /// Aggregate a bag of numeric values using the given subtotal function.
    /// Matches the ScDPAggData semantics:
    ///   sum / product / min / max / count : trivial
    ///   countNums : count of numeric entries (identical to count here because
    ///     the caller only places parsed numerics into the bag)
    ///   average : arithmetic mean
    ///   stdDev  : sample std-dev  (sqrt(Σ(x-μ)²/(n-1))), requires n≥2
    ///   stdDevp : population std-dev (sqrt(Σ(x-μ)²/n)), requires n≥1
    ///   var     : sample variance (Σ(x-μ)²/(n-1)), requires n≥2
    ///   varp    : population variance (Σ(x-μ)²/n), requires n≥1
    /// Returns 0 for empty input and for stdDev/var when n&lt;2, matching the
    /// existing 0-on-empty convention that the rest of the renderer assumes.

View on GitHub (pinned to 1ced45e900)

Solutions

  1. Use a valid token from the message: sum, count, countNums, average/avg, max, min, product, stdDev/std, stdDevp/stdp, var/variance, varP
  2. Omit the function segment to use the default 'sum': values=Sales
  3. Check the aggregate= override list for typos if you use that sibling property

Example fix

// before
values="Sales:tottal"
// after
values="Sales:sum"
Defensive patterns

Strategy: validation

Validate before calling

var validAgg = new[] { "sum", "count", "countnums", "countnum", "average", "avg",
    "max", "min", "product", "stddev", "stdev", "std", "stddevp", "stdevp", "stdp",
    "var", "variance", "varp" };
if (!validAgg.Contains(func.ToLowerInvariant()))
    throw new InvalidOperationException($"Invalid aggregate '{func}'");

Type guard

static readonly HashSet<string> ValidAgg = new(StringComparer.OrdinalIgnoreCase)
{ "sum", "count", "countnums", "countnum", "average", "avg", "max", "min", "product",
  "stddev", "stdev", "std", "stddevp", "stdevp", "stdp", "var", "variance", "varp" };
static bool IsValidAggregate(string s) => ValidAgg.Contains(s);

Try / catch

try { BuildPivot(props); }
catch (ArgumentException ex) when (ex.Message.Contains("invalid aggregate"))
{ /* correct the token against the valid list */ }

Prevention

When it happens

Trigger: values=Sales:tottal (typo); values=Sales:mean (not an alias — use 'average' or 'avg'); values=Sales:stdeviation (too long — use 'stddev' or 'std'); aggregate=tottal,sum override list with a typo.

Common situations: Typos; users entering Excel UI function labels that do not match the CLI vocabulary; mismatches between the aggregate= override list and the values= list length exposing an unintended token.

Related errors

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/45cda08544909c9d. Report an issue: GitHub.