Summing from Closed Workbooks



The following formula was created to sum a variable number of values in row 3 of Book1, Sheet1, starting at column A:
=SUM(OFFSET(‘C:\My Documents\[Book1.xls]Sheet1′!$A$3,0,0,1,A2))
However, the formula only works correctly when Book1 is open.
When Book1 is closed, the formula returns #VALUE!.
How can we modify the formula so that it works regardless of whether Workbook1 is open or not?


Use the SUM, COLUMN, and INDIRECT functions as shown in the following Array formula:
IF(ISNUMBER(‘C:\My Documents\[Book1.xls]Sheet1′!$A$3:$Z$3),’C:\My Documents\[Book1.xls]Sheet1′!$A$3:$Z$3))}

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>