I have had a little luck naming the spreadsheet tab name the same name as the
workbook (works if you only have data on one tab).  If  you repost the
formula replacing all the link files with "file 1, file 2, ...", it would be
a little easier to follow.

GTVT06 wrote...

...

(reformatted replacing your pathname with [PN])
=SUM(SUMIF('[PN]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31:E31),
'[PN]Spreadsheet'!$D$5:$D$35)-SUMIF('[PN]Spreadsheet'!$A$5:$A$35,
"<="&LOOKUP(TODAY()-7,KC!B31:E31),'[PN]Spreadsheet'!$D$5:$D$35))
/(SUMIF('[PN]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31:E31),
'[PN]Spreadsheet'!$C$5:$C$35)-SUMIF('[PN]Spreadsheet'!$A$5:$A$35,
"<="&LOOKUP(TODAY()-7,KC!B31:E31),'[PN]Spreadsheet'!$C$5:$C$35))

The length of the pathname isn't the only problem. Using single quotes,
' , in Excel workbook filenames is a bad idea because they need to be
doubled when used in formulas because Excel also uses single quotes to
enclose workbook/worksheet names that contain spaces. This was
exceptionally shortsighted on the part of the Excel developer(s) who
did this, but we're stuck with it.

Next, even if your formula weren't too long, SUMIF would evaluate to
#REF! errors if the workbook you're refering to were closed. That's
because SUMIF only accepts as its 1st and 3rd arguments what Excel
considers ranges, and as far as Excel is concerned, ranges only exist
in OPEN files.

So if you intend to use this formula when the file is closed, you can't
use SUMIF.

Eliminate the useless use of SUM and the formula becomes

=(SUMIF('[PN]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31:E31),
'[PN]Spreadsheet'!$D$5:$D$35)-SUMIF('[PN]Spreadsheet'!$A$5:$A$35,
"<="&LOOKUP(TODAY()-7,KC!B31:E31),'[PN]Spreadsheet'!$D$5:$D$35))
/(SUMIF('[PN]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31:E31),
'[PN]Spreadsheet'!$C$5:$C$35)-SUMIF('[PN]Spreadsheet'!$A$5:$A$35,
"<="&LOOKUP(TODAY()-7,KC!B31:E31),'[PN]Spreadsheet'!$C$5:$C$35))

Both numerator and both denominator terms use similar conditions,

'[PN]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY(),KC!B31:E31)

and

'[PN]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY()-7,KC!B31:E31)

Both could be rewritten together as

'[PN]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY()-{0,7},KC!B31:E31)

Then reflect that the 1st indicates addition and the 2nd subtraction.

('[PN]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY()-{0,7},KC!B31:E31))*{1,-1}

This evaluates to a 31 row by 2 column array of +1s and 0s in the 1st
column and -1s and 0s in the second column. Multiply them rowwise
against [PN]Spreadsheet's columns D and C and sum the respective
results to get the numerator and denominator terms. Easiest to do that
using MMULT.

=SUM(MMULT(TRANSPOSE(('[PN]Spreadsheet'!$A$5:$A$35
<=LOOKUP(TODAY()-{0,7},KC!B31:E31))*{1,-1}),'[PN]Spreadsheet'!$D$5:$D$35))
/SUM(MMULT(TRANSPOSE(('[PN]Spreadsheet'!$A$5:$A$35
<=LOOKUP(TODAY()-{0,7},KC!B31:E31))*{1,-1}),'[PN]Spreadsheet'!$C$5:$C$35))

Now replace the [PN] token with the full pathname.

=SUM(MMULT(TRANSPOSE(('C:\Open folder\Dummy folder\Default\Sub
Folder\Another Folder\2nd to last folder\last folder "06\[Name of
spreadsheet
''06.xls]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY()-{0,7},KC!B31:E31))*{1,-1}),
'C:\Open folder\Dummy folder\Default\Sub Folder\Another Folder\2nd to
last folder\last
folder "06\[Name of spreadsheet ''06.xls]Spreadsheet'!$D$5:$D$35))
/SUM(MMULT(TRANSPOSE(('C:\Open folder\Dummy folder\Default\Sub
Folder\Another
Folder\2nd to last folder\last folder "06\[Name of spreadsheet
''06.xls]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY()-{0,7},KC!B31:E31))*{1,-1}),
'C:\Open folder\Dummy folder\Default\Sub Folder\Another Folder\2nd to
last folder\last
folder "06\[Name of spreadsheet ''06.xls]Spreadsheet'!$C$5:$C$35))

Remove the newlines and this is spans a mere 741 chars. It's also needs
to be entered as an array formula.