Hello,

I'm trying to determine the maximum value in column Data!G:G, that matches date in English!B2, but matches a series of skill referenced in A2-A4. I can use the following formula to get what I need, but I'd like it to be more dynamic so that changing skills would be easier:

=MAX(MAXIFS(Data!G:G,Data!$A:$A,English!$B2,Data!$B:$B,{"1","2","3"}))

Is there a way to accomplish this while referencing cells instead of text? For example (this doesn't work BTW):

=MAX(MAXIFS(Data!G:G,Data!$A:$A,English!$B2,Data!$B:$B,{"$A$2","$A$3","$A$4"}))

Ultimately I'd like to something similar to the following which captures all data meeting a date criteria and any criteria in A2:A100 (which happen to be skill numbers).

=SUMPRODUCT(SUMIFS(Data!D:D,Data!$A:$A,English!$B2,Data!$B:$B,English!$A$2:$A$100)) - Where Data!A:A are dates, and Data!B:B are skill numbers. English A2:A100 contains all the skills in a particular group.