excelloopslambdaexcel-formulaexcel-lambda

Is it possible to step through a row of data and sum every n-th cell using a Lambda function?


I have a model that produces an array of values that can be several hundred columns wide. Every 25th column contains a number that I need to add to the total.

I thought the cleanest solution would be creating a LAMBDA function that would take the starting cell from the user's input and then offset across the row 25 cells, add the value there to a running total, and continue the offset stepping until it reached an empty cell.

Is it possible? How to do it?


Solution

  • You should be able to do this with a recursive LAMBDA.

    StridingSum = LAMBDA(cell, step, [start],
        LET(
            colOffset, IF(ISOMITTED(start), -1, start) + step,
            value, OFFSET(cell, 0, colOffset),
            IF(
                value = "", 
                0,
                value + StridingSum(cell, step, colOffset)
            )
        )
    );
    

    Which you call as =StridingSum(A2, 25) (assuming your data started in A1)