Search code examples
excelexcel-formulaexcel-tables

Table Auto-Expand Changes Formula Range

The attached image is a table in a workbook I'm working out of. It's pretty self explanatory if you read the column headers. The Week number has a simple count formula to count the number of Week Ending dates in the range from the top of the data to that specific row. i.e - for Row one of the data the count range is $B$2:B2, for row 27 of the data the range is $B$2:B28

The issue is that when a new row is added the range for the new row is correct, $B$2:B29, but the range in row 27 changes to the exact same range, hence the 2 rows with a 28 count. I can't ferret out an answer from google and as far as Excel is concerned it's formula is fine... Can anyone provide some insight on this?

The formula is =COUNT($B$2:B##) depending on what row its on for those who want to see it.

enter image description here

Solution

  • You could use INDEX:

    =COUNT($B$2:INDEX(B:B,ROW()))

    The issue is in the old last row Excel thinks that you want to include the whole data set as it refers to such. So when the new row is added it adds to the formula automatically.

    With the formula above it will not do that.