Formulas changing over time

[Ralter649]

I have formulas that adds up numbers in a range. The size of the table changes day to day, normally about 200 rows, so originally I wrote the ranges to be $R$5:$R$500 to have space to grow.. Today I noticed that it had changed to $R$5:$R$146. I have this same formula 30 times, with minor changes to it, threw the table and ALL of the formulas are changing at the same rate, every one of them now says $R$146 instead of $R$500. I save this program daily under a new name. I went back and looked at the older saves and over time the number slowly drops. Sometimes 1 a day or sometime 15 a day. There is no pattern.


Any thoughts or ideas of how to fix this would be appreciated



=SUMIFS($R$5:$R$146,$S$5:$S$146,"",$Q$5:$Q$146,"6")+SUMIFS($S$5:$S$146,$S$5:$S$146,"<>",$Q$5:$Q$146,"6")

 

[Marcílio_Lobão]

Ralter649, Good afternoon.

Is there any inconvenience in keeping the range limit at $R$500?

For the performance of the SUMIF function this will not change practically anything, but for you to be constantly changing the formulas this will bring a lot of inconvenience and time spent working with something unimportant.

 

[Ralter649]

I am not changing the formula, i was fine with it being$R$500, overtime something in the program is making it change little by little.

 

[Eric W]

Are you deleting rows from your worksheet? If so, Excel will adjust any formulas that reference those rows. If you have

=SUM(A1:A10)

and delete rows 4 and 5, it will become

=SUM(A1:A8)

If you have that situation, and you want to keep a lower limit, there are a few options. If there's nothing else in the column, you can use a whole column reference, like

=SUM(A:A).

But you have to be careful with that! Some functions (SUM, or SUMIF) internally keep track of the actual lowest used row and use that, so they are efficient. Other functions (SUMPRODUCT) do not keep track of the lowest row, so they use the maximum row (1,048,576), which can lead to some VERY slow formulas!

You could also define your range as a table. The formula would look something like:

=SUM(Table1[Count])

Again, Excel would keep track of the actual bottom row regardless of adding/deleting rows.

Finally you could use INDIRECT or OFFSET to define your range. You could code your formula in such a way that it always uses 500 rows, or even get fancier and look for the actual bottom row used. You could even use them in conjunction with the Name manager and have a formula like:

=SUM(MyRange)

But that method is also potentially slow, depending on the situation.

 

[Ralter649]

thanks for the help