Using Sumproduct, Countif/sumif, & Indirect on outside workbook

I am currently trying to calculate data from 5 separate outside workbooks for averages, totals and counts for various tasks completed throughout the day over the course of multiple days. The circumstances are that people fill out these worksheets daily showing what they have accomplished as well as how long each task took. In a separate workbook i am trying to calculate the average amount of time these tasks took over a 2 week window. However, i am getting a reference error when i try to accomplish this. Below is the formula that i am using to try to accomplish this.

the named range that i am using is the dates in text format to reference the tabs on the workbook that i wish to count.

column A contains the name of the tasks & D2 is the name of the task that i want to look up.

=sumproduct(countif(indirect("'[workbook.xlsx]"&namedrange&"'!A4:a5000"),D2

Any and all suggestions are welcome!!! Thank you!

Looks like you have an extra single quote in there.
=sumproduct(countif(indirect("'[workbook.xlsx]"&namedrange&"'!A4:a5000"),D2

i don't believe so it is broken down =sumproduct(countif(indirect("double quote 'single quote [bracket workbook name]bracket "double quote &And Named range &and "double quote 'single quote !exclamation range),cell). Is this wrong?