Dynamically SUM across Excel sheets based on cell text value

Question

I am attempting to dynamically sum the same cell across multiple sheets based on the text content of another cell. In one cell, the file will always say "Data through [month name] [year]". I have a formula that extracts the month, but I can't figure out how to nest it into my other formula, which also works when it is static.

Here is my formula to extract the month name:

=MID(A2,14,LEN(A2)-18)

Here is my formula to statically sum across sheets, which are named with month names:

=SUM(January:December!J5)

Here is the faulty formula that I am working with:

=SUM("January:" & (MID(A2,14,LEN(A2)-18)) & "!J5")

I have also tried:

=SUM(INDIRECT("January:" & MID(A2,14,LEN(A2)-18) & "!J5"))

Does anyone have any tips for me?

Thanks in advance!

EDIT:

After editing, I now have a formula which will give me the sum of the first and last month, but not those in between.

=SUM(INDIRECT("January! J5"),INDIRECT(MID(A2,14,LEN(A2)-18)&"!J5"))

In short, I need to replace that comma with the equivalent of a colon, though neither : nor ":" works.

Answer 1

Put the name of the sheets in order somewhere, then use this formula:

=SUMPRODUCT(SUM(INDIRECT( D1:INDEX(D:D,MATCH(MID(A2,14,LEN(A2)-18),D:D,0)) & "!J5")))

I put my list starting in D1 and going down.