Counta changes cell references when delete or add row

[4653]

I am using =COUNTA($B$2:$B$4000) to determine the number of cells that are not blank in cell range B2 through B4000. Row one is a frozen row so it always remains visible if that matters and the COUNTA is in cell B1. Every time I insert a row anywhere in the range B2 to B4000 it changes B2 to B3 in the COUNTA formula and if I delete a row it will change back from B3 to B2. I want the range within the formula to remain constant regardless of whether I insert rows or delete them. Any help would be much appreciated.

 

[TheVillageIdiot]

I am using =COUNTA($B$2:$B$4000) to determine the number of cells that are not blank in cell range B2 through B4000. Row one is a frozen row so it always remains visible if that matters and the COUNTA is in cell B1. Every time I insert a row anywhere in the range B2 to B4000 it changes B2 to B3 in the COUNTA formula and if I delete a row it will change back from B3 to B2. I want the range within the formula to remain constant regardless of whether I insert rows or delete them. Any help would be much appreciated.

Try,

=COUNTA(INDIRECT("$B$2:$B$4000"))

 

[T. Valko]

Try,

=COUNTA(INDIRECT("$B$2:$B$4000"))

Since the range references within the INDIRECT function are quoted this evaluates to a TEXT string and as such, a text string will never change if the formula is copied. So, we can eliminate the dollar signs $:

=COUNTA(INDIRECT("B2:B4000"))

The range will automatically be evaluated as an absolute range.

Another way to do it:

=COUNTA(INDEX(B:B,2):INDEX(B:B,4000))