Sum data across multiple sheets using index match and indirect

Question

I have multiple tabs with account balances within one excel spreadsheet. All sheets are set up exactly the same. I would like to sum the end of month account balances across all sheets. I would use sumifs but the difficulty is that there can be more than one balance data for the same day in which case I would need the formula to take the last one (furthest down in the column where the balances are held). I have tried index match with indirect as follows:

{SUM(INDEX(INDIRECT("'"&Sheets&"'!"&"$AJ$5:$AJ$250"),MATCH(C$2,INDIRECT("'"&Sheets&"'!"&"$B$5:$B$250"),0)))}

Named range "Sheets" holds the names of all tabs that I need the sum to work.

AJ$5:$AJ$250 is where the balances are within the "Sheets".

B$5:$B$250 is where the balance dates are within the "Sheets".

C$2 (row 2) is the end of month criteria on the summary sheet based on which I would like to find and sum the balances.

The above formula only picks up the value from the first sheet within the "Sheets" named range.

Would appreciate help how to correct.