Using the COUNTIF function to link workbooks- Please Help!!

  • Thread starter Thread starter Katie
  • Start date Start date
K

Katie

Hello,

I did a countif formula where the criteria is based on the
results of formulas in a different workbook, but for some
reason it's only linking to the other workbook if that
other workbook is open. Once it's closed it can't read
the file and I get a #VALUES error message.

I've tried to automatically update the link, reopening or
changing the links name, and it doesn't work.

The weird thing is, the formula will pull the data from
the other workbook if it's not a COUNTIF function. It
will pull the info with a COUNTA function.
 
Now you have learned that countif does not work with closed workbooks,
it would have helped if you would have posted your countif formula since you
can
replace it with sumproduct that will work with closed workbooks. As an
example

=COUNTIF('C:\Documents and
Settings\xxxx\Desktop\[Myfile.xls]Sheet4'!$D$2:$D$13,">5")

can be replaced with

=SUMPRODUCT(--('C:\Documents and
Settings\xxxx\Desktop\[Myfile.xls]Sheet4'!$D$2:$D$13>5))
 
I tried that and it didn't work. Here is the formula:

=COUNTIF('G:\WIL\Queue 970\[Queue_970_0115AB.xls]Queue 970
Audit'!$R$4:$R$15000,Summary!$A$1)

-----Original Message-----
Now you have learned that countif does not work with closed workbooks,
it would have helped if you would have posted your countif formula since you
can
replace it with sumproduct that will work with closed workbooks. As an
example

=COUNTIF('C:\Documents and
Settings\xxxx\Desktop\[Myfile.xls]Sheet4'!$D$2:$D$13,">5")

can be replaced with

=SUMPRODUCT(--('C:\Documents and
Settings\xxxx\Desktop\[Myfile.xls]Sheet4'!$D$2:$D$13>5))

--

Regards,

Peo Sjoblom


Hello,

I did a countif formula where the criteria is based on the
results of formulas in a different workbook, but for some
reason it's only linking to the other workbook if that
other workbook is open. Once it's closed it can't read
the file and I get a #VALUES error message.

I've tried to automatically update the link, reopening or
changing the links name, and it doesn't work.

The weird thing is, the formula will pull the data from
the other workbook if it's not a COUNTIF function. It
will pull the info with a COUNTA function.
Click to expand...


.
Click to expand...
 
=SUMPRODUCT(--('G:\WIL\Queue 970\[Queue_970_0115AB.xls]Queue 970
Audit'!$R$4:$R$15000=Summary!$A$1))

--

Regards,

Peo Sjoblom


I tried that and it didn't work. Here is the formula:

=COUNTIF('G:\WIL\Queue 970\[Queue_970_0115AB.xls]Queue 970
Audit'!$R$4:$R$15000,Summary!$A$1)

-----Original Message-----
Now you have learned that countif does not work with closed workbooks,
it would have helped if you would have posted your countif formula since you
can
replace it with sumproduct that will work with closed workbooks. As an
example

=COUNTIF('C:\Documents and
Settings\xxxx\Desktop\[Myfile.xls]Sheet4'!$D$2:$D$13,">5")

can be replaced with

=SUMPRODUCT(--('C:\Documents and
Settings\xxxx\Desktop\[Myfile.xls]Sheet4'!$D$2:$D$13>5))

--

Regards,

Peo Sjoblom


Hello,

I did a countif formula where the criteria is based on the
results of formulas in a different workbook, but for some
reason it's only linking to the other workbook if that
other workbook is open. Once it's closed it can't read
the file and I get a #VALUES error message.

I've tried to automatically update the link, reopening or
changing the links name, and it doesn't work.

The weird thing is, the formula will pull the data from
the other workbook if it's not a COUNTIF function. It
will pull the info with a COUNTA function.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top