Search code examples
excelexcel-formulamultiplicationsubtractionexcel-lambda

Formula to automatically subtract and multiply two rows over a variable number of columns

I have the following sum in G4 =SUM((G2:G3)*$D$2) and in G6 I have =SUM(G4-G5), I then drag copy them for the duration denoted in C1. I'd like to change this so I have a single formula in H4 and H6 that will expand based on the value in C1.

I have something similar in another sheet that sums two adjacent cells together for the duration of a project but I just can't see how to modify it to work for the multiplication sum in H4 and the subtraction sum in H6 on this sheet. This is what I have in the other sheet =BYCOL(INDEX($1:$4,{3;4},SEQUENCE(1,D2,COLUMN(I:I))),LAMBDA(x,SUM(x)))

Auto Multiply and Subtract adjacent cells

Here's the data I used:

Start Date  End Date    Length in Months    Rate
01/01/2021  31/12/2021  12                  £3,500

And:

Jan-21  Feb-21  Mar-21  Apr-21  May-21  Jun-21  Jul-21  Aug-21  Sep-21  Oct-21  Nov-21  Dec-21
Staff   2   3   2   1   6   2   1   2   5   5   3   2
Additional Staff                1       2       3       4       
Costs   £5,000  £7,000  £5,000  £2,000  £8,000  £7,500  £1,500  £7,000  £7,000  £15,000 £5,000  £2,000
Solution

  • I have the sum in G4 working using =BYCOL((INDEX($1:$4,{2;3},SEQUENCE(1,C2,COLUMN(G:G)))),LAMBDA(x,SUM(x*($D$2)))).

    And for G6 I have a workaround by multiplying costs row from G5 by -1 which makes the following work =BYCOL((INDEX($1:$5,{4;5},SEQUENCE(1,C2,COLUMN(G:G)))),LAMBDA(x,SUM(x)))

    Ideally I wouldn't have to multiply the costs by -1 so it'd be great to hear of alternative solutions.