A formula issue on Excel. I will try to explain what I want and what I did and my troubleshooting steps as well. This puzzle is getting to me now. Need to solve it.

Requirement:
I have similar data in sheets named 1 to 30. Now in a summary sheet I have to sum one column from each sheet and show the sum amount (if a condition is met). The condition is a word in my summary sheet needs to match with a cell in a column in any of the sheets and then another column value of the same row of the same sheet gets added. This needs to be done for a range of sheets that is also user defined (anywhere from 1 to 30)
The sheets are just named "1", "2" and so on till "30". The Start of the range is at C7 and the end at C8 of the summary sheet. The condition is to sum/add E30 to E49 from the range of sheets, when B42 of the summary sheet is present anywhere in F30 to F49 in the range of sheets.

Trial:
=SUMPRODUCT(IF(ISNUMBER(MATCH(B42, INDIRECT("'" & ROW(INDIRECT("1:" & C8 - C7 + 1)) + C7 - 1 & "'!F30:F49"), 0)), INDIRECT("'" & ROW(INDIRECT("1:" & C8 - C7 + 1)) + C7 - 1 & "'!E30:E49"), 0))

This formula I think will dynamically sum the values from cells E30 to E49 across the specified range of sheets, conditional on the presence of B42 in the range F30 to F49.

However it does not work. It returns 0 despite matches being present in multiple sheets.

Troubleshoot:
=ROW(INDIRECT("1:" & $C$8 - $C$7 + 1)) + $C$7 - 1

This works perfectly and returns an array of numbers from C7 to C8.

=MATCH(B42, INDIRECT("'X'!$F$30:$F$49"), 0)

This works perfectly as well. It returns 1 if a match is found in Sheet X (1 to 30) but it is for one sheet at a time.

=SUMPRODUCT(IF(ISNUMBER(MATCH(B42, INDIRECT("'1'!$F$30:$F$49"), 0)), INDIRECT("'X'!$E$30:$E$49"), 0))

This works perfectly as well. It adds all the values to the adjacent cell(in E) when B42(summary sheet) matches a cell from F30 to F49(particular sheet X) X is 1 to 30.

Problem:

However the trail formula doesn't work. Either the formula is flawed (which I think should work) or the array formula is not working. I have tried entering with command + return / command + return + shift but I do not know whether that worked or not. I am new to using such functions. I am not going to use helper columns as it is too time consuming and messy.

Anyone who read so far, Thank you.