I have a problem with countifs cell references moving when I use the sort function in the Sort & Filter (from the Editing tools). I have a data set containing 19 columns and 45 rows of data. I pull data into the table using a combination of sumifs and countifs statements depending on the data type.

The primary cell reference follows all of the sumifs statements regardless of how I sort the data (using the column filters). The primary cell reference does not follow the countifs statements ? every time I sort using the column filters to sort, the primary cell reference sticks to the content of the original cell, so the cell reference ends up offset from the intended cell.

I've also had the same problem with an xlookup statement as well.

I just don?t understand why it works sometimes, and misbehaves other times.