SUMPRODUCT Help

[AYouQueTai]

Hello,

The following formula (In G2) is giving me a result of "0" when it should display the player's first name.
The reason for the indirect reference is every team will have their own sheet like Buffalo.
=SUMPRODUCT((INDIRECT($D2&"[Start Date]")>=[@Date])*(INDIRECT($D2&"[End Date]")<=[@Date])*(INDIRECT($D2&"[Jersey]")=[@Jersey]),(INDIRECT($D2&"[First Name]")))

Can anyone tell me what I'm doing wrong?

NHL 2024-2025.xlsx
	A	B	C	D	E	F	G	H	I
1	GameId	Date	Home/Away	Team	Jersey	Pos	First Name	Last Name	Full Name
2	020001	Oct-04	Home	Buffalo	02		0
Roster

Cell Formulas
Range	Formula
B2	B2	=IF([@GameId]="","",VLOOKUP([@GameId],Schedule,3,FALSE))
D2	D2	=IF($A2="","",IF($C2="Away",VLOOKUP([@GameId],Schedule,5,FALSE),VLOOKUP([@GameId],Schedule,6,FALSE)))
G2	G2	=SUMPRODUCT((INDIRECT($D2&"[Start Date]")>=[@Date])*(INDIRECT($D2&"[End Date]")<=[@Date])*(INDIRECT($D2&"[Jersey]")=[@Jersey]),(INDIRECT($D2&"[First Name]")))

Cells with Data Validation
Cell	Allow	Criteria
C2:C1048576	List	=INDIRECT("HomeAway")

NHL 2024-2025.xlsx
	A	B	C	D	E	F	G
1	Jersey	Start Date	End Date	Pos	First Name	Last Name	Full Name
2	02	Oct-03-2024	Oct-04-2025		Test
3	05	Oct-03-2024	Oct-04-2025		Test 5
Buffalo

 

[DanteAmor]

Sumproduct returns numbers, it cannot return texts.
To use indirect, you must reference the sheet and a range and you are referencing a table.

Try the next one. I put a range of 100 rows. You can adjust it.

=FILTER(INDIRECT("'"&[@Team]&"'!$E$2:$E$100"),(INDIRECT("'"&[@Team]&"'!$B$2:$B$100")<=[@Date])*(INDIRECT("'"&[@Team]&"'!$C$2:$C$100")>=[@Date])*(INDIRECT("'"&[@Team]&"'!$A$2:$A$100")=[@Jersey]))

 

[AYouQueTai]

That is Great! Thank you very much.

 

[AYouQueTai]

Is there a way to make the Ranges Dynamic?
For Example $E$2:$E$100 will always be [First Name] in the Team's worksheet and table.
$B$2:$B$100 will always be [Start Date] etc..

 

[DanteAmor]

This would be an alternative.
In the K:L columns (or in the columns that you have available), put the names of the sheets and their respective table name.
Then use the following formula:

=LET(a,VLOOKUP([@Team],K:L,2,0),FILTER(INDIRECT(a&"[First Name]"),(INDIRECT(a&"[Start Date]")<=[@Date])*(INDIRECT(a&"[End Date]")>=[@Date])*(INDIRECT(a&"[Jersey]")=[@Jersey])))

Ex:

 

[AYouQueTai]

Thanks again