Not to argue this is better for your situation than Power Query or VBA (it's "kludgy"... a brute force approach, if you will), but as to the having of a line for each page matching the label format, and a total below them, but presumably without 300 blank lines between those two, you CAN use a simple approach to overcome that.
The idea is related to the old "how do I search a range, but allow for increasing size of the range?" problem. Say you have 50 such pages currently and will have 5-10-15-however-many a month added to the list. As long as the tab names have to follow a pattern and order, you simply build the summary page with a block of formulas that look for the information on, say, 400 sheets so more than now existent and enough for some time to come.
To avoid the annoying error messages in the lines referring to sheets that do not exist, use IFERROR to blank them out and you will have a big, long, blank section. That still leaves you with the blanks though.
Instead, test for error with IFERROR. If the first cell in the summary row for that (eventual) sheet errors, then use IF to test the column's cell just above. If that cell's contents match what its lookup (repeated here, not referenced!) generates, then this row gets the summary row's formulas for its TRUE output. If it does not, so you know its referred to sheet does not exist AND the summary row should not go here, then blank the cell (with "" for example).
Cells in a row to the right are less intensive as they can look at the first cell for their guidance.
So sheets that exist will have a row whose formulas show the appropriate figures. The first row that looks to a sheet that does not exist will have the summary formulas active. Nice summary right under the active data. Further rows will have blanks in them and look nice and empty.
This is reasonably robust as one can show mud on one's detractor's fins by showing how they named a sheet wrongly. Then fix it with a simple renaming.
However, it is not a solution you will point to with pride if you can instead choose to use Power Query. And since it looks like all or most all of the Power Query work can be done with the mouse, not keyboard (no writing of SQL here and there), I suggest using that! Kick in a quick macro recorder macro to update the table and a button to trigger the macro to, and you'll look quite slick.
However, as a quick thing (improvement can always come later... unless your organization is unwilling to "mess with success" in search of better), and an easy thing, clunky though this may be, it will serve.
Too bad they don't want just summation, rather than listing all sheets and their appropriate information, then the summation, because then you could use an old technique, that of placing one sheet (perhaps named "Start") before all the pages to sum and one following all of them (perhaps named "End"), then writing formulas that look like:
=SUM( 'Start'!A1:'End'!A1 )
and neatly catch everything in between the two, regardless of name. If one could reliably keep the new sheets between the first and last (placeholder, never to have content themselves) sheets, then the new ones are just new additions to the list of sheets Excel will look at, but additions you never need explicitly make Excel aware of. Easy peasy. This is not your situation though so... unless it could become it?
