if i'm not wrong:
=SUMPRODUCT((INDIRECT("'"&$O$12&"'!F2:F19998")='Weekly Performance'!C2)*(INDIRECT("'"&$O$12&"'!J2:J19998")))

it will still show a REF error because you don't have a May4 sheet. when you do, it should work fine