Countif

  • Thread starter Thread starter Master T
  • Start date Start date
M

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)
 
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
 
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
 
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.
 
Jacob Skaria said:
Replace Book1, Sheet1 and Sheet2 to suit your requirement

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

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.
 
Back
Top