An implementation of a SUM function for ClosedXml

ClosedXml-Sum.cs

ce.RegisterFunction("SUM", 1, int.MaxValue, Sum, FunctionFlags.Range, AllowRange.All);

private static AnyValue Sum(CalcContext ctx, Span<AnyValue?> args)
{
    var sum = 0.0;
    foreach (var nullableArg in args)
    {
        if (!nullableArg.HasValue)
            continue;

        var arg = nullableArg.Value;
        if (arg.TryPickScalar(out var scalar, out var collection))
        {
            var conversionResult = ctx.Converter.CovertToNumber(in scalar);
            if (!conversionResult.TryPickT0(out var number, out var error))
                return error;

            sum += number;
        }
        else
        {
            var valuesIterator = collection.TryPickT0(out var array, out var reference)
                ? array
                : reference.GetCellsValues(ctx);
            foreach (var value in valuesIterator)
            {
                // collections ignore strings and logical, only numbers (and errors) allowed
                if (value.TryPickNumber(out var number))
                    sum += number;
                else if (value.TryPickError(out var error))
                    return error;
            }
        }
    }

    return sum;
}