COUNTIFS - Change formula for closed external workbook

[JDobbsy1987]

Hi,

I have been using a countifs formula which works great, but now want to reference to an external workbook which only works whilst they're open. Can any one help change this formula to be able to work with closed external workbooks please?

=COUNTIFS('[BOX - Master.xlsx]Master'!$I:$I,"done")+COUNTIFS('[BOX - Master.xlsx]Master'!$I:$I,"completed")

Regards
Jamie

[Raphaelp]

You have to enter the file path for the external workbook

=COUNTIFS('*enter path here*[BOX - Master.xlsx]Master'!$I:$I,"done")+COUNTIFS('*enter path here*[BOX - Master.xlsx]Master'!$I:$I,"completed")

such as C:\Docs\... etc.

[ImranBhatti]

I assume both ov workbooks are in the same directory.
then you may refference I column of closed workbook into any column(say I column) of the active workbook then you may count your criteria text from the active workbook with this formula

=COUNTIF(I:I,"done")+COUNTIF(I:I,"completed")

[JDobbsy1987]

Hi,

I do have the path as well, sorry it disappeared as I had the workbook open. The problem is, when the external workbook is closed it changes to a "#Value!" error.

Regards.

[JDobbsy1987]

Sorry to double post, I have seen online that this should be replaced to a sumproduct or sum(if( array formula however I cannot get either to work nor do I understand so I am struggling to do so.

Any help would be appreciated.

Regards.

[ImranBhatti]

This would have been my approach.First bring data from closed workbook and then count them. See the attachement.
Result is in the A1 Cell of OpenedWB