Keeping subtotal averages in a changing sorted table

Hi there,

Here's my issue. I am analyzing a mountain of data on different apartment buildings, and need average rents at all the buildings. It is all in the same worksheet, seperated into the different apartments.

I'm trying to sort the data into 1 BR, 2 BR, and 3 BR, and then analyze based on averages. Naturally, because the data is so huge, I sorted each building into a table, and in order to make it easier want to sort into 1 BR, calculate the average....and so on.

But I've encountered two issues. When I sort the data, I need it to take the actual row the data is in (1BRs and 2 BR's are not in order in data) because when I drag, it shows up as say A7:A74, which is fine while its sorted, however once I sort it differently it screws it up. *I should note I know I could just put that number in, however I am looking to make this tool dynamic for future analysis.

So the question is, how do I get my average to only show the specific rows that the filtered rows are numbered as, not as a large range?

Thanks in advance for any and all of your help!!

I think it'd be easiest to avoid sorting and subtotals and just perform the calculations you want on a different sheet (or just somewhere else). You can use the "=AVERAGEIF" and/or "=AVERAGEIFS" functions to determine the relevant criteria you'd like to average. If you're not familiar with these functions, feel free to upload a sample file and we can provide some examples on how to write the formulas.