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)

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


On 29 Kwi, 09:35, Master T wrote:
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)

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
---------------
Jacob Skaria


"Master T" wrote:

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)

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

Select the cell
Goto the menu FormatCellsNumber tabGeneralOK
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.

--
Biff
Microsoft Excel MVP


"Master T" wrote in message
...
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)

Jacob Skaria wrote...
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.