CMB wrote...
....
1. Using the OFFSET function:

=SUM(OFFSET('[Daily-Data.xls]Daily'!$F$5,273,0,303,1))
....
2. Using the INDEX function:

=SUM(INDEX('[Daily-Data.xls]Daily'!$F$5:$F$369,274):
INDEX('[Daily-Data.xls]Daily'!$F$5:$F$369,302))
....
Both of these formulas work fine when the source workbook is open.
....

These two formulas sum different ranges. The OFFSET formula sums
F278:F580 while the INDEX formula sums F278:F306. Which do you want?

The short but technical answer is that you're using both OFFSET and
INDEX to return range references, but external references into closed
workbooks *NEVER* resolve to range references, rather they're returned
as arrays.

You need to use something like the following.

=SUMPRODUCT('[Daily-Data.xls]Daily'!$F$1:$F$580,
(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)=k)
*(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)<=n))

If you want to sum F278:F580, make it

=SUMPRODUCT('[Daily-Data.xls]Daily'!$F$1:$F$580,
(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)=278)
*(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)<=580))