CountIF Closed Workbook

[Pardeep]

Hi,

I've read the previous posts however none have really been able to fix the problem I'm having.

I have a shared spreadsheet that colleagues use daily (MAILBOX LOG), however I want a master spreadsheet that will pull data from each worksheet withing the MAILBOX LOG worksheet.


The current COUNTIF I have is:

=COUNTIF('J:\Business Support\Mailbox Spreadsheet\[Mailbox Log.xlsx]November'!G:G,"Right of Withdrawal")

This will only wok when the workbook is open

I've read previous posts and assumed the following would work:
=COUNTIF[('J:\Business Support\Mailbox Spreadsheet\[Mailbox Log.xlsx]November!$G$1:$G$500,"Right of Withdrawal")

but I get the error message saying the formula the formula doesnt start with an Underscore or Letter

ANY HELP?!!

 

[steve the fish]

Hi

Try:

=COUNTIF('J:\Business Support\Mailbox Spreadsheet\[Mailbox Log.xlsx]November'!$G$1:$G$500,"Right of Withdrawal")

 

[Aladin Akyurek]

COUNTIF, a range-processing function, does not work with closed books. You need an array-processing formula:

=SUMPRODUCT(('J:\Business Support\Mailbox Spreadsheet\[Mailbox Log.xlsx]November!$G$1:$G$500="Right of Withdrawal")+0)

 

[Pardeep]

Hi,

Thanks for the replies. It seems to still be pulling up the same error message:

Reasons for thsi can include:
The name does not begin with a letter or an underscore
the name contains a space or other invalid characters
the name conflicts with an Excel built-in name or the name of another object in the workbook


do you guys think its easier to maybe have a graph to pull the data from the closed workbook?

 

[Aladin Akyurek]

Hi,

Thanks for the replies. It seems to still be pulling up the same error message:

Reasons for thsi can include:
The name does not begin with a letter or an underscore
the name contains a space or other invalid characters
the name conflicts with an Excel built-in name or the name of another object in the workbook


do you guys think its easier to maybe have a graph to pull the data from the closed workbook?

=SUMPRODUCT(('J:\Business Support\Mailbox Spreadsheet\[Mailbox Log.xlsx]November'!$G$1:$G$500="Right of Withdrawal")+0)

 

[Pardeep]

Thanks Aladin Akuyrek!

This has worked!!!

 

[Aladin Akyurek]

Thanks Aladin Akuyrek!

This has worked!!!

You are welcome.