How can I add another requirement to this query?

TLDR: Formula and summary of question/problem at the bottom if you want to skip ahead.

I have a sheet where names are in column A and B. There are numerical values in columns C, D, E, and F.

Newest values are at the bottom (oldest at the top).

What I want to do is for the most recent 10 entries of a name in columns A & B, I want to average C (when the name is in column A) and D (when the value is in column D).

However, the caveat is I also want to get the column C value **ONLY** when column F is a certain thing (ie when "greater than 100"). And similarly, I want column D ONLY when the corresponding column E value is the same (>100).

Basically I want value of C2 when A2 is the name of the person and when F2 is greater than ___.
And with that same query I want value of D when B2 is the name and when E2 is greater than ___.
Not a typo - it's A/C/F and B/D/E.

ALSO, I DO NOT want the last 10 times the condition of BOTH A/F or B/E are met. I want the last 10 instances of the name, and if let's say I look for the most recent 10, and there are only one or two times where the E/F column requirements are met, then that's all I'd want returned.

My formula right now is this.

Please Login or Register  to view this content.

In this case, A2 references the name I am using in my query, as a heads up.


^ This gets me the combined average of column C (when A2 is in column A of the Data sheet) and column D (when A2 is in column B of the Data sheet).

Now what I need to do is add the second part of the query...how do I get this Average query into an Averageifs one, where it also only returns the value if column F is greater than ___ if A2 is in column A and only returns the value if column E is greater than ___ if A2 is in column B.