counting criteria in closed workbook

hello, I have a workbook in which I need to pull in data from several closed workbooks. INDIRECT and COUNTIFS don't work with closed workbooks as far as I can ascertain.

I've had success with SUMPRODUCT while the path, file, tab, range are all hard coded into the formula.
This all falls apart when I try to bring in variables.

The workbooks to be interrogated all have the same file name, sheet names, headings etc. They only differ by the folder they are located in. The folder names I need to reference are listed in range of cells in my active workbook.

I need to bring a COUNT of matching criteria from these workbooks based on values in several columns.
As outlined, when I hard code the formula, all is well, but get errors (#REF), zero or the text of the formula I'm trying to create... I'll give example:

- path, always the same, "\\server\common folder\path"
- folder, variable (based on range of cells), "2024 R"
- sheet, always the same, "data"
- first critera range, will refer to whole column (no range names, no tables), "$M;$M" (="Active")
- second criteria range, will refer to whole column (no range names, no tables), "$C;$C" (=$A$1 on the active sheet)

works perfectly:
=SUMPRODUCT(('\\server\common folder\path\2024 R\[financial result.xlsx]data'!$M:$M="Active")*('\\server\common folder\path\2024 R\[financial result.xlsx]data'!$C:$C=$A$1))

when I try anything to incorporate the variable "2024 R" into the formula I get a differing errors (depending on what I'm trying).

I would really appreciate any help with this.

I am open to VBA or PowerQuery if that is a better option... (but would still require how to do this)

Thank you

How are you adding the variable to the formula? Are you simply trying to add a cell reference?

Try this:

=SUMPRODUCT(INDIRECT("('\\server\common folder\path"&variable&"\[financial result.xlsx]data'!$M:$M="Active")")*IDIRECT("('\\server\common folder\path"&variable&"\[financial result.xlsx]data'!$C:$C=$A$1)"))

where variable is a cell reference.

It would be easier if you shared two small sample workbooks for testing.

There was a typo in my formula. Try again:

=SUMPRODUCT(INDIRECT("('\\server\common folder\path"&cellreference&"\[financial result.xlsx]data'!$M:$M="Active")")*INDIRECT("('\\server\common folder\path"&cellreference&"2024 R\[financial result.xlsx]data'!$C:$C=$A$1)"))

If not, then provide the samples I mentioned.