<div>
User Sumproduct that won't produce an error if the referenced workbook is closed.<br />
Change your formula to this....<br />
<br />

<pre><code id="code-20-40955261-1">=SUMPRODUCT(--('T:\CenterDocumentSystem\[CDS_Document List.xls]AED'!$E$2:$E$13&lt;TODAY()))</code></pre>
</div>


User Sumproduct that won't produce an error if the referenced workbook is closed.
Change your formula to this....



=SUMPRODUCT(--('T:\CenterDocumentSystem\[CDS_Document List.xls]AED'!$E$2:$E$13<TODAY()))

<div>
sktneer,<br />
<br />
Thank you for your quick response.<br />
<br />
I took one parameter for granted and failed to mention that the range has blank cells.<br />
<br />
The formula you provided works except it is also counting the blank cells in the range.<br />
<br />
I am locked out of the worksheet in the formula.<br />
<br />
please advise further.<br />
<br />
Thank you<br />
<br />
aasikolo.
</div>


sktneer,

Thank you for your quick response.

I took one parameter for granted and failed to mention that the range has blank cells.

The formula you provided works except it is also counting the blank cells in the range.

I am locked out of the worksheet in the formula.

please advise further.

Thank you

aasikolo.

<div>
Excellent Solution. &nbsp;Thank you!
</div>


<div>
You're welcome aasikolo! Glad I could help.
</div>


You're welcome aasikolo! Glad I could help.

<div>
<div class="content wysiwyg-content">
Good Afternoon,<br />
<br />
I have a summary sheet in a separate workbook with the following function:<br />
<br />
=COUNTIF('T:\CenterDocumen<wbr />tSystem\[C<wbr />DS_Documen<wbr />t List.xls]AED'!$E$2:$E$13,&quot;<wbr />&lt;&quot;&amp;TODAY()<wbr />)<br />
<br />
When the workbook specified in the formula is closed, I receive a #VALUE! error.<br />
<br />
Is there a way to count the cells in the range and criteria &nbsp;I'm interested in when the workbook is closed?<br />
<br />
If so, please provide the syntax.<br />
<br />
Thank you.<br />
<br />
aasikolo
</div>
</div>


Good Afternoon,

I have a summary sheet in a separate workbook with the following function:

=COUNTIF('T:\CenterDocumentSystem\[CDS_Document List.xls]AED'!$E$2:$E$13,"<"&TODAY())

When the workbook specified in the formula is closed, I receive a #VALUE! error.

Is there a way to count the cells in the range and criteria  I'm interested in when the workbook is closed?

If so, please provide the syntax.

Thank you.

aasikolo

Excel =countif wont work on external workbooks that are closed.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.