Formula with SUMPRODUCT,SUMIFS and INDIRECT is not returning the expected results.

Try this in I7 and fill down. Copy and paste to other sections.

Formula:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&{"SD";"SD2"}&"'!F2:F100"),INDIRECT("'"&{"SD";"SD2"}&"'!C2:C100"),$D7,INDIRECT("'"&{"SD";"SD2"}&"'!A2:A100"),$B7,INDIRECT("'"&{"SD";"SD2"}&"'!B2:B100"),$C7,INDIRECT("'"&{"SD";"SD2"}&"'!E2:E100"),$A7,INDIRECT("'"&{"SD";"SD2"}&"'!D2:D100"),$E7&"*"))