Can I get row count to increment in the spill?

Question

Level 2

197 points

Can I get row count to increment in the spill?

I've got a table size currently 27 columns by 3000 rows (size will likely grow in both directions). The last column is a sum across the row of every other column entry. Rather than drag the yellow dot to populate the last column I thought to use makearray to make a column with the lambda function to do the sum (type it once, no dragging). That works fine in doing the sum. However the spill does not increment the row number (e.g. ...+P2+ ...) so the entire column is the first entry. Is there some way to get the lambda function row count to increment as it spills?

Mac mini, macOS 26.3

Posted on Mar 18, 2026 8:59 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,966 points

Mar 18, 2026 10:16 AM in response to DesertRatR

If you don't mind doing it as a simple addition you can do it as =B+C+D+ and so on.

or (using columns B:D as an example):

=BYROW(B:D,LAMBDA(element,SUM(element)))

Reply

Answer 2

Camelot

Level 9

59,205 points

Mar 18, 2026 10:31 AM in response to DesertRatR

I'm not quite sure what you need MAKEARRAY() to do this, vs. just having a footer row in your table... that said, it's your sheet, not mine, so who am I to judge.

The following formula pasted in the top cell of column AA (#27) will do what I think it sounds like you're asking:

=MAKEARRAY(ROWS(AA),1,LAMBDA(row_index,column_index,SUM(INDIRECT("R"&row_index&"C1:R"&row_index&"C"&COLUMNS($1:$1,1)−1,FALSE))))

The first arguments to MAKEARRAY() are the dimensions of the array you want. The number of rows matches the number of rows in this column, so that's easy (or ROWS(AA)-1 if you put this formula in row 2 to skip a header row.

For the LAMBDA() function, you're passed in the row_index and column_index, and the calculation is

SUM(INDIRECT("R"&row_index&"C1:R"&row_index&"C"&COLUMNS($1:$1,1)−1,FALSE))

This first runs INDIRECT() to build a range reference. It does this by combining the row_index of the array to build a R1C1-style reference, where the second column reference is determined by the COLUMNS($1:$1) -1

So for the first row, this would resolve to:

=SUM(R1C1:R1C26)

and the row number would increment as you work down the column. Since you want all columns and all rows have the same number of columns, it's easy to base the formula on the number of columns in row $1 (minus 1 for the sum column itself) since you don't really care about which row you're counting columns in.

Either way, this one formula will expand to cover the ever-growing list of rows and columns (although it depends on this formula being in the last column, otherwise it will break.

If I've misinterpreted the ask, please feel free to post back with more details.

Reply

Answer 3

Badunit

Level 7

30,966 points

Mar 18, 2026 4:38 PM in response to DesertRatR

Personally I think a simple C+E+G+...+Z would be the most clear, concise, and easy to understand for what appears to be less than 13 items to add up and it spills into the other rows as requested, but here is an array formula that I think works. I'd double check it against the addition just to be sure:

=BYROW(FILTER(C:Z, MOD(COLUMN(C:Z), 2) = 1),LAMBDA(element,SUM(element)))

Reply

Answer 4

DesertRatR Author

Level 2

197 points

Mar 18, 2026 4:20 PM in response to Camelot

I think I was not clear about what I am asking for. Col 27 is the sum of every other element in that row beginning with R2C2 (Row 1 are the headers, Col A and B are other stuff) i.e. c2+e2+g2+... +z2, then Row 3 i.e. c3+e3+g3+...+z3, all the way to the bottom (Row 3000). I like the idea of makearray because no dragging the yellow dot, enter it in AA2 and it should just spill and fill. That simplifies as the table gets longer, and easy to modify if table gets wider. I am also new to lambda and find this interesting. Never pass up an opportunity to learn.

Reply