Dynamic Indirect Formula with Two Countif Formula Results in One Cell

[quibilty]

In the attached example, I'm looking for three different formulas, one is a dynamic lookup based on a date, and the other two are a dynamic lookup that incorporates a countif function.

1) For worksheet "(MatchUp)" cells C3 & C4, I'm looking to display a value that correlates to the date listed in "(MatchUp)" cell B2 and [worksheet "Alb" or "AP"] columns A and P. In the "(MatchUp)" tab, since cell B2 has a value of "06-02-25", a value of "5" is returned in cell C3 because in the "Alb" worksheet there is a value of "5" in cell A26 and a value of "06-02-25" in cell P26.

2) For worksheet "(MatchUp)" cell F3, I'm looking to display two countif formula results in one cell.

a) In order to return the result of "1-2" in cell F3, here's how each number is calculated:
-For the number on the left side of the hyphen in "(MatchUp)" tab cell F3, because cell E3 is "Alb", AND since cell K3 is "10.5", AND cell I3 is "Conf, Away" THEN I'm looking to display the number of times the value in worksheet "Alb" column F is >=10.5 AND there is an "@" in column Q (this represents "Away") AND there is a "Yes" in column D (this represents "Conference"), AND there is a value <0 in column E.
-For the number on the right side of the hyphen in "(MatchUp)" tab cell F3, because cell E3 is "Alb", AND since cell K3 is "10.5", AND cell I3 is "Conf, Away", THEN I'm looking to display the number of times the value in worksheet "Alb" column F is >=10.5 AND there is an "@" in column Q AND there is a "Yes" in column D, AND there is a value >0 in column E.

b) In order to return the result of "0-3" in cell G3, here's how each number is calculated:
-For the number on the left side of the hyphen in "(MatchUp)" tab cell G3, because worksheet "(MatchUp)" cell E3 is "Alb", AND since cell J3 is "158.5", AND cell I3 is "Conf, Away" THEN I'm looking to display the number of times the value in worksheet "Alb" column J is >=158.5 AND there is an "@" in column Q (this represents "Away") AND there is a "Yes" in column D (this represents "Conference"), AND the value in column H is >0.
-For the number on the right side of the hyphen in "(MatchUp)" tab cell G3, because worksheet "(MatchUp)" cell E3 is "Alb", AND since cell J3 is "158.5", AND cell I3 is "Conf, Home", THEN I'm looking to display the number of times the value in worksheet "Alb" column J is >=158.5 AND there is an "@" in column Q AND there is a "Yes" in column D, AND the value in column H <0.

In cells F6, F7, G6, & G7 I've shown what the results would be if the variables in I3 & I4 were both changed to "Conf".

All cells in blue represent cells that will have data manually input by the user.

[Gregb11]

1) Will cell e3 always = "Alb" and e4 always = "AP" or might you type in either in either cell. If the later, you could try this in C3:
=XLOOKUP($B$2,INDIRECT(E3&"!P3:P1000"),INDIRECT(E3&"!A3:A1000"),"",0)

This also assumes you are looking for an exact match on the date, which is sounds like you do. However, your answer in cell C4 is 4, but there is no date of 2/6/2025 in sheet AP, but there is a 4 next to 2/5/25, so maybe you want the earliest one from the date given. In that case, you could try this in cell C4:
=XLOOKUP($B$2,INDIRECT(E4&"!P3:P1000"),INDIRECT(E4&"!A3:A1000"),"",-1)

2) The 2 examples you gave both have "Conf, Away". Are you only supposed to count the ones with "@" in Col "Q" if the word "Away" is in cell I3? From the drop-down, what if it doesn't say Home or Away?

[quibilty]

1) the data in E3 and E4 will be variables (they'll change from Alb, to AP, to CT, and ME and so on...).
2) good catch, that's an error. The date 2/6/2025 SHOULD appear in worksheet "AP" cell P26, but instead it says 2/5/2025.
3) Each value in worksheet "(MatchUp)" cell I3, I4, I6, I7, and so on... will be one of the ten options contained in the drop down list.
If the text "Conf" (i.e. conference) shows in worksheet "(MatchUp)" cell I3, then the formula is looking for the text "Yes" in column D in worksheets [Alb/AP/etc].
If the text "OOC" (i.e. out of conference) shows in worksheet "(MatchUp)" cell I3, then the formula is looking for no text in column D in worksheets [Alb/AP/etc].
If the text "Home" shows in worksheet "(MatchUp)" cell I3, then the formula is looking for not text in column Q in worksheets [Alb/AP/etc].
If the text "Away" shows in worksheet "(MatchUp)" cell I3, then the formula is looking for "@" in column Q in worksheets [Alb/AP/etc].
If the text "Neutral" shows in worksheet "(MatchUp)" cell I3, then the formula is looking for "N" in column Q in worksheets [Alb/AP/etc].
If the text "Conf, Away" shows in worksheet "(MatchUp)" cell I3, then the formula is looking for "@" in column Q and the text "Yes" in column D in worksheets [Alb/AP/etc].
If the text "Conf, Home" shows in worksheet "(MatchUp)" cell I3, then the formula is looking for "" in column Q and the text "Yes" in column D in worksheets [Alb/AP/etc].
and so on...

[Gregb11]

Maybe this in F3 (and copy down)

=LET(rng,INDIRECT(E3&"!P3:P1000"),PPD,INDIRECT(E3&"!E3:E1000"),x,(INDIRECT(E3&"!Q3:Q1000")=IF(ISNUMBER(SEARCH("Away",I3)),"@",IF(ISNUMBER(SEARCH("Home",I3)),"","N")))*(INDIRECT(E3&"!F3:F1000")>=K3)*(INDIRECT(E3&"!D3:D1000")=IF(ISNUMBER(SEARCH("Conf",I3)),"Yes","")),
IFERROR(ROWS(FILTER(rng,x*(PPD<0))),0)&"-"&
IFERROR(ROWS(FILTER(rng,x*(PPD>0))),0))

[quibilty]

The formula for F3/F4 works great when the data in I3/I4 is ALWAYS "Conf, Away" in I3 and "Conf, Home" in I4. I was wondering, is it possible to write the formula so that the results change when the data in I3/I4 change (there are ten drop down options)?

Also, I don't need the date formula, after all. Thank you for the formula, any way.

[quibilty]

Actually, I think I can simplify the requirements, and hopefully make this an easier task.

For worksheet (WS) "(MatchUp)"
LOOK AT data in cells E3 & K3...
RETURN the following two results based on that data:
1) For the value on the left side of the hyphen: How many times does the following occur: value in [WS "Alb" column F] >= value in [WS "(Matchup)" cell K3] AND value in [WS "Alb" column E] <0.
2) For the value on the right side of the hyphen: How many times does the following occur: value in [WS "Alb" column F] >= value in [WS "(Matchup)" cell K3] AND value in [WS "Alb" column E] >0.

Disregard values in I3 altogether.

[Gregb11]

If that is all it is, then if F3 try:
=COUNTIFS(INDIRECT("'"&E3&"'!F3:F1000"),">="&K3,INDIRECT("'"&E3&"'!e3:e1000"),"<0")&"-"&COUNTIFS(INDIRECT("'"&E3&"'!F3:F1000"),">="&K3,INDIRECT("'"&E3&"'!e3:e1000"),"<0")

Then copy down.