hi Steve. not sure what are you trying to achieve. usually, people use it in a case where they might have several sheets like:
April4
May4
Jun4

so say i have "April4" in A2. then i would use:
=SUMPRODUCT((INDIRECT(A2&"!F2:F20001")='Weekly Performance'!C4)*(April4!$J$2:J20001))

the above would be equivalent to your first formula. but if i change A2 to "May4", then it becomes an equivalent of:
=SUMPRODUCT((May4!$F$2:F20001='Weekly Performance'!C4)*(April4!$J$2:J20001))

and why do you fix the $F$2 portion. are you going to copy down & make it F2:F20002?