Cell Reference COUNTIFS & XLOOKUP

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.

Sorry - hopefully this sample data is not to large

The columns in yellow highlight are the columns that follow when I sort the range by Sales $ (or any other column)

Thanks for the solution JeteMc! I'll try this. Any thoughts on why this is an issue sometimes but not in all cases?