Game Balance error in excel using countif, indirect, and variable for sheet name.

[Dreppan]

Hello, everyone. I have successfully done the separate parts of this operation but am getting repeated errors combining them. I need to have one workbook (Overview.xlsx) look for a specific name within a range on another (DesignData.xls) - when all instances of that name are found, it needs to then add every number directly to the left of those cells into one sum. Furthermore I need the name of each sheet in DesignData searched to be dynamic, read from the top of the column of Overview (Images enclosed).

I keep getting a #REF ! error with just the below partial command:

=COUNTIF(INDIRECT("'" & B2 & "'!$D$19:$X$24"),A3)

Thanks in advance,
Scott