Averageif using Index Match

[nathanthomson]

In the attached excel snippet, I show sales from 2022 & 2023. I'm looking to pull the average sales during the golf season (May - September) and average sales in the off season for 2022 & 2023 combined. For example, in the table cell B13 "Golf Season - Alcohol Sales", I would want to see the average of F3 - J3 & R3 - V3 ($6,000). I'm working on a large document so ideally find a formula that references both column header and row header so I can drag it.

I apologize, I am on my work network and am unable to download XL2BB.

 

[hagia_sofia]

Hello, please test this:

=LET(
months,B2:Y2,
sales,B3:Y5,
list,TEXT(SEQUENCE(,12)*28,"mmmm"),
seq,SEQUENCE(,12),
numbers,XLOOKUP(months,list,seq),
season,(numbers>=5)*(numbers<=9),
HSTACK(BYROW(FILTER(sales,season=1),LAMBDA(a,AVERAGE(a))),BYROW(FILTER(B3:Y5,season=0),LAMBDA(a,AVERAGE(a)))))

 

[nathanthomson]

Hello, please test this:

=LET(
months,B2:Y2,
sales,B3:Y5,
list,TEXT(SEQUENCE(,12)*28,"mmmm"),
seq,SEQUENCE(,12),
numbers,XLOOKUP(months,list,seq),
season,(numbers>=5)*(numbers<=9),
HSTACK(BYROW(FILTER(sales,season=1),LAMBDA(a,AVERAGE(a))),BYROW(FILTER(B3:Y5,season=0),LAMBDA(a,AVERAGE(a)))))

That appears to have worked. Thank you!