Formula Range Changes When Deleting Rows.

[ac66bronco]

I have a file for tracking where certain loans are while I maintain them. I use sum() and count() formulas to help me make sure I have everything.

But what happens is after some time of deleting rows the range in the formulas no longer captures all the data and they need to be readjusted.

IE. =COUNTIFS($G$17:$G$6210,"H*",$B$17:$B$6210,B12,$I$17:$I$6210,"19") will eventually become
=COUNTIFS($G$17:$G$621,"H*",$B$17:$B$621,B12,$I$17:$I$621,"19")

(please note the data may only go from row 17 to row 800 but I use a large range due to my current problem)

same with the sum() formulas.

Is there anyway to protect the range from changing when deleting rows?

 

[Snakehips]

@ac66bronco Try expressing your ranges using index like below.

=COUNTIFS(INDEX(G:G,17):INDEX(G:G,800),"H*",INDEX(B:B,17):INDEX(B:B,800),B12,INDEX(I:I,17):INDEX(I:I,800),"19")

HTH

 

[ac66bronco]

@ac66bronco Try expressing your ranges using index like below.

=COUNTIFS(INDEX(G:G,17):INDEX(G:G,800),"H*",INDEX(B:B,17):INDEX(B:B,800),B12,INDEX(I:I,17):INDEX(I:I,800),"19")

HTH

Working beautifully. I suspected an index would be the solution I am just not that great at setting them up. I think i understand them a bit better now. Thank you so much.

 

[awoohaw]

If you keep you data in a table, and use the column names as references instead of ranges in formulas the problem would not exist either.

 

[ac66bronco]

If you keep you data in a table, and use the column names as references instead of ranges in formulas the problem would not exist either.

Curious. Can you explain or show how? If you have the time.

 

[awoohaw]

tables automatically expand and contract as table rows and columns are added. Sometimes (in Data Validation for example) you can still use the cell references instead of column references.

create a table (CTRL-T)

Change Table Name:

So this is how table column name or range referencing works:
(the formulas you see in D2 and E2, are what are in D3 and E3, these formulas "SPILL")

In A8 and B8, when you start to add a new row in the table (must be the very next cell)
Both references in D2 and E2 are updated when you enter the first value in the new row (cell A8)
(Well technically the reference in D2 is not visibly updated although the data is. But you can certainly see that E2 is!)

What this means is the any formulas that you use when reference a table will automatically expand or contract as your data does.

NOTE: Some features in excel may not work in earlier versions. Please update your profile so your excel version appears next to your posting button (see how mine says "365"?).

 

[Trixterz]

Another option is to use the indirect formula.

=COUNTIFS(INDIRECT("$G$17:$G$6210",TRUE),"H*",INDIRECT("$B$17:$B$6210",TRUE),B12,INDIRECT("$I$17:$I$6210",TRUE),"19")

 

[awoohaw]

Another option is to use the indirect formula.

=COUNTIFS(INDIRECT("$G$17:$G$6210",TRUE),"H*",INDIRECT("$B$17:$B$6210",TRUE),B12,INDIRECT("$I$17:$I$6210",TRUE),"19")

I avoid volatile functions whenever possible. I'm not sure if table columns expansion is volatile or not though.

 

[Trixterz]

Its the opposite actually, the indirect formula is not a volatile function, any specified ranges within an indirect formula is locked, and will never dynamically change even if you delete rows within the affected formula ranges. I am guessing that this is what the user was looking for.

 

[awoohaw]

Its the opposite actually, the indirect formula is not a volatile function, any specified ranges within an indirect formula is locked, and will never dynamically change even if you delete rows within the affected formula ranges. I am guessing that this is what the user was looking for.

that is very interesting. Could you provide the links and documentation when it is not? And how it would not be if used in the context of this question?

and will never dynamically change even if you delete rows

I'm not sure if that matters. INDIRECT Recalculates entirely with every calculation action.