CountIF Closed Workbook

Pardeep

New Member
Joined
Nov 26, 2014
Messages
3
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Hi

Try:

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

New Member
Joined
Nov 26, 2014
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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)
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top