Dynamic SUMIFS

[daveyc18]

=SUMIF($K$5:$K$111,"*C",L$5:L$111)*100+SUMIF($K$5:$K$111,"*S",L$5:L$111)-SUMIF($K$5:$K$111,"*P",L$5:L$111)*200


sometimes more rows of data are added so i need to adjust L111 to , say L130

 

[arthurbr]

You can make your range an Excel Table,rebuild your formula based on the table. Any row added/deleted will extend/shorten the range automatically.

 

[FormR]

sometimes more rows of data are added so i need to adjust L111 to , say L130

Besides using a formal table (probably the better option) - is there a reason why you can't just specify a sufficiently large range, say a 1000 rows?

FWIW, you can also write your formula a little more succinctly, like this.

=SUMPRODUCT(SUMIF($K$5:$K$1000,{"*C","*S","*P"},L$5:L$1000),{100,1,-200})

 

[daveyc18]

Besides using a formal table (probably the better option) - is there a reason why you can't just specify a sufficiently large range, say a 1000 rows?

FWIW, you can also write your formula a little more succinctly, like this.

=SUMPRODUCT(SUMIF($K$5:$K$1000,{"*C","*S","*P"},L$5:L$1000),{100,1,-200})

i don't want to make too many extra rows and like to keep it tight

 

[arthurbr]

So, again, you should use an Excel Table

 

[FormR]

I agree that a table is the way to go, but there's no real impact to performance with that formula using extra rows, or even full column references.