COLUMNS() does not work in SCAN function, but COLUMN() does?

Question

This formula resets the accumulator after each row of a four-columns range and works perfectly fine. It resets the accumulator on column 1 of each row:

=SCAN(0, $A$105:$D$106, LAMBDA(accum, b, IF(MOD(COLUMN(b), 4) = 1, b, accum+b)))

If you try to dynamically count the columns, however, it does not work - COLUMNS always returns 1.

=SCAN(0, $A$105:$D$106, LAMBDA(accum, b, IF(MOD(COLUMN(b), COLUMNS(b)) = 1, b, accum+b)))

Can anyone explain this behavior? I would expect either COLUMNS(b) to return 4, or the COLUMN(b) statement to always return 1. It seems strange that COLUMN works, but COLUMNS doesn't?

Answer 1

That makes sense, I hadn't thought of that. Thanks.

Answer 2

If you want to minimize the number of external cell references you might need to modify, try a LET formula:

=LET(input, $A$105:$D$106, firstColumn, MIN(COLUMN(input)), SCAN(0, input, LAMBDA(accum,b, IF(COLUMN(b)=firstColumn, b, accum+b))))

Only one reference to the input array at the start, and everything else is defined in terms of that.

Answer 3

Ah, I see, although that rather torpedo's the original purpose, which was to handle the original table if it grew extra columns without needing to modify the formula too much.

My original misinterpretation was the COLUMN function it appears. I tested on A-D and thought it was the column number of the range, but it's actually the absolute column number of the worksheet. If I move the original range to columns B-E instead, then the COLUMN function fails also.

Thanks for the reply - it pointed me in the right direction. I guess to handle that I'd have to use:

=SCAN(0, $A$105:$D$106, LAMBDA(accum,b, IF(MOD(COLUMN(b)-COLUMN(A105)+1,COLUMNS($A$105:$D$106)) = 1,b, accum+b)))

Thanks again.

Answer 4

What's happening is that the SCAN formula sends each cell to the lambda function one at a time. The 'b' in your lambda is always the single cell that is being calculated at that point, and a single cell will only ever have one column.

What you want is to check against the number of columns in the whole array:

=SCAN(0, $A$105:$D$106, LAMBDA(accum, b, IF(MOD(COLUMN(b), COLUMNS($A$105:$D$106)) = 1, b, accum+b)))