average based on column condition in excel

Question

I'm trying to find the average number for a specific number of columns that will be changable. For example, in the below I want to find a way where I can get the average for each month for a select number of columns (here Alice and John). This however may change so I'm trying to make it as easy to change as possible (so make it Bob, John & Jo for example)

I can do SUMPRODUCT for one but it doesn't seem to allow me to have multiple column names in there. In an ideal world, I could have something whereby I could change "Alice, John" to any combination that will work. I can think of how to do this in R but not excel; maybe because it's a Friday afternoon. Thanks in advance.

Answer 1

If one has Office 365:

=AVERAGE(FILTER(B2:E2,ISNUMBER(SEARCH(","&SUBSTITUTE($B$1:$E$1," ","")&",",","&SUBSTITUTE($F$1," ","")&","))))

If not:

=AVERAGE(IF(ISNUMBER(SEARCH(","&SUBSTITUTE($B$1:$E$1," ","")&",",","&SUBSTITUTE($F$1," ","")&",")),B2:E2))

If not using Office 365 Excel, this will need to be confirmed with Ctrl+Shift+Enter instead of Enter when exiting edit mode.

Or:

=SUMPRODUCT(SUMIFS(B2:E2,$B$1:$E$1,FILTERXML("<t><s>"&SUBSTITUTE($F$1,",","</s><s>")&"</s></t>","//s")))/COUNTA(FILTERXML("<t><s>"&SUBSTITUTE($F$1,",","</s><s>")&"</s></t>","//s"))