Formula with range how to add adding another row in that range-CodePudding

I have this complexed formula that I need to update manually every time I add a new row to that range:

=SUMIFS('actions!O3:O21,'actions!B3:B21,"buy",'actions!C3:C21,"UK")*-1 SUMIFS('actions!N3:N21,'actions!B3:B21,"buy",'actions!C3:C21,"UK")*-1

I wonder what is the best solution to handle dynamic ranges inside formulas without using app script?

Thanks

EDIT: I'm unable to do as the comment suggested because the last row is saved for sums.

EDIT 2: I created an example sheet link

CodePudding user response：

try:

=SUMIFS(actions!O3:O, actions!B3:B, "buy", actions!C3:C, "UK")*-1 
 SUMIFS(actions!N3:N, actions!B3:B, "buy", actions!C3:C, "UK")*-1

update 1:

if this formula is meant to be in the same row as the total sum row try:

=SUMIFS(INDIRECT("actions!O3:O"&ROW()-1), 
        INDIRECT("actions!B3:B"&ROW()-1), "buy", 
        INDIRECT("actions!C3:C"&ROW()-1), "UK")*-1 
 SUMIFS(INDIRECT("actions!N3:N"&ROW()-1), 
        INDIRECT("actions!B3:B"&ROW()-1), "buy", 
        INDIRECT("actions!C3:C"&ROW()-1), "UK")*-1

update 2:

if you just want the whole range without the last row:

=SUMIFS(INDIRECT("actions!O3:O"&ROWS(A:A)-1), 
        INDIRECT("actions!B3:B"&ROWS(A:A)-1), "buy", 
        INDIRECT("actions!C3:C"&ROWS(A:A)-1), "UK")*-1 
 SUMIFS(INDIRECT("actions!N3:N"&ROWS(A:A)-1), 
        INDIRECT("actions!B3:B"&ROWS(A:A)-1), "buy", 
        INDIRECT("actions!C3:C"&ROWS(A:A)-1), "UK")*-1

or if the above won't do try:

=INDEX(
 SUMIFS(INDIRECT("actions!O3:O"&MAX(ROW(A:A)*(A:A<>""))-1), 
        INDIRECT("actions!B3:B"&MAX(ROW(A:A)*(A:A<>""))-1), "buy", 
        INDIRECT("actions!C3:C"&MAX(ROW(A:A)*(A:A<>""))-1), "UK")*-1 
 SUMIFS(INDIRECT("actions!N3:N"&MAX(ROW(A:A)*(A:A<>""))-1), 
        INDIRECT("actions!B3:B"&MAX(ROW(A:A)*(A:A<>""))-1), "buy", 
        INDIRECT("actions!C3:C"&MAX(ROW(A:A)*(A:A<>""))-1), "UK")*-1)