Using Countif on closed workbooks

[DaveSwanton77]

Is is possible to use the countif statement on closed work books.

I would like to count the occurances of certain entries on books that are closed to give a grand total.

It calculates fine when the workbook in question is open. But when the workbook is closed the result changes to #value!.

I don't have the problem with the counta statement, but this will just give me the product of the whole column.

Thanks in advance

Dave

 

[Mark W.]

Is is possible to use the countif statement on closed work books.

No. Consider using...

{=COUNT(IF(range=value,1))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

 

[DaveSwanton77]

re:

Mark,

Thanks for the reply but unfortunately this does not work

The formula I have entered is

{=COUNT(IF([RemarksSPBOs.xls]Details!$AF:$AF="CL",1))}

which gives a total of 0

Whereas

=COUNTIF([RemarksSPBOs.xls]Details!$AF:$AF,"CL")

gives a total of 57

 

[Mark W.]

By definition Details!$AF:$AF isn't a range. A range consists of individual cell references joined by a colon ). For example, Details!AF2:AF100. Details!$AF:$AF is a reference to all cells in column AF. This is explained in the Excel Help topic for "About cell and range references".

 

[DaveSwanton77]

Thanks very much for taking the time out Mark

I have now got it to work

Thanks again

Dave

 

[Mark44stfc]

This is nice

 

[kannan73]

This didn't seem to work for me... the formula I am using is =COUNTIF([April 02,2015.xlsx]Dog Food'!$B$4:$B$200="*as*") and it comes up with a value of 12 but when I use {=COUNT(IF([April 02,2015.xlsx]Dog Food'!$B$4:$B$200="*as*",1))} it gives me a value of 0. I'm trying to count the amount of times as comes up in that range but there are times it appears with other letters and / in front of or behind it hence the *