excelexcel-formulaexcel-lambda

Automatically add a number of row cells and subtract a 3rd row over a variable number of columns in Excel

I have the following function that automatically sums 3 rows together for a specified number of cells determined by D2:

=BYCOL((INDEX($1:$11,{4;5;10},SEQUENCE(1,D2,COLUMN(I:I)))),LAMBDA(x,SUM(x)))

The values in each column in rows 4 and 5 I wish to add together and I'd like to subtract the result from the cell value for row 10. I have a workaround which sets the values in row 10 to negative but I'd prefer them to be positive numbers.

I tried the following, but unfortunately it doesn't work:

=BYCOL((INDEX($1:$11,{4;5;10},SEQUENCE(1,D2,COLUMN(I:I)))),LAMBDA(x,y,z,SUM((x+y)-z)))

Example of expected result:

Jan Feb Mar Apr May Jun Jul Aug Sept
Revenue 10000 12000 14000 16000 11000 20000 21000 22000 24000
Extra Revenue 1000 2000 3000 1000 2000 2000 1000 1500 2500
Costs 500 1000 2000 500 1000 1000 750 1000 1000
Profit/Loss 10500 13000 15000 16500 12000 21000 21250 22500 25500
Solution

  • To make it match with your data:

    =BYCOL((INDEX($1:$11,{2;3;4},SEQUENCE(1,Q2,COLUMN(B:B)))),LAMBDA(x,INDEX(x,1)+INDEX(x,2)-INDEX(x,3)))

    where I've put a suitable value (9) into Q2.

    enter image description here

    EDIT

    This would have been tidier

    =BYCOL((INDEX($1:$11,{2;3;4},SEQUENCE(1,Q2,COLUMN(B:B)))),LAMBDA(x,SUMPRODUCT(x,{1;1;-1})))