Countif

[Master T]

Greetings,
I'm having a problem using countif function. I'm trying count values from
two separate spreadsheet. I'm not getting any result, it returns the formula
in the cell.

here is the formula i'm using below
=countif('[Primecure_Providers.xls]Network GP & PC Medicentre'!$A:$A,A2)

 

[Jarek Kujawa]

possible reasons:
1. as far as I know COUNTIF works with open workbooks only
2. replace Network GP & PC Medicentre with Network_GP_&_PC_Medicentre
3. CTRL+` (or Tools->Options->View tab->Window options, uncheck
Formulas) to switch

 

[Jacob Skaria]

Replace Book1, Sheet1 and Sheet2 to suit your requirement

=COUNTIF([Book1]Sheet1!$A:$A,A2)+COUNTIF([Book1]Sheet2!$A:$A,A2)

If this post helps click Yes

 

[T. Valko]

If the cell displays the formula then the cell is probably formatted as
text.

Select the cell
Goto the menu Format>Cells>Number tab>General>OK
With the cell still selected hit function key F2 then hit enter

The formula should now work if the source file is open, however, COUNTIF
doesn't work on closed files. Use SUMPRODUCT instead.

=SUMPRODUCT(--('[Primecure_Providers.xls]Network GP & PC
Medicentre'!$A$1:$A$100=A2))

However (!!!!!), SUMPRODUCT *can't* use entire columns as range references
unless you're using Excel 2007.

 

[Harlan Grove]

Replace Book1, Sheet1 and Sheet2 to suit your requirement

=COUNTIF([Book1]Sheet1!$A:$A,A2)+COUNTIF([Book1]Sheet2!$A:$A,A2)

....

Problem is that COUNTIF would return #REF! if the referenced workbook
(s) is(are) closed. When working with external references, best to use
SUMPRODUCT rather than either COUNTIF or SUMIF.

That said, if all one wants to do is count, and if the range(s) to be
counted is(are) all numbers and the criteria, A2, is also a number,
then you could use a single FREQUENCY call.

=INDEX(FREQUENCY('X:\y\[z.xls]Sheet1:Sheet2'!$A:$A,A2*
{0.999999999999999;1}),2)

FREQUENCY, unlike COUNTIF, does handle external references into closed
workbooks.