Using FILTER inside SUBTOTAL in google sheets

Question

I have a table in google sheets with columns including "year", "industry", "total carbon footprint" and "carbon footprint per employee" . The table has filters so you can look at the average carbon footprint for different industries for example.

I would like to have a summary that gives the average footprint per employee for each year, which can then be filtered by industry, i.e. if I filter the table by industry I can see the average footprint per employee for 2019, 2020, 2021 etc. for that industry.

I had assumed I could use SUBTOTAL and FILTER, e.g. for 2020 summary:

=SUBTOTAL(101,FILTER(G8:G,B8:B="2020"))

And, although this filter works on it's own, within this formula it does not think that G8:G is a range. I am sure there is an obvious way to do this - what am I doing wrong?

Answer 1

Try

=AVERAGE(FILTER(G8:G,B8:B="2020"))

Or

=AVERAGEIF(B8:B,"2020",G8:G)