I'm having a problem with Excel not updating references in a countif
formula to another workbook. Other spreadsheets are working fine, but
this one I have isn't updating properly.

Automatic calculation is turned on and even doing a force with F9
doesn't make any difference. The only way I can make it pull in the
values from the other workbook is if I open those other work books.
This isn't going to be a problem for another week, but being that they
are doing a calculation based on a workbook that is created daily its
going to be a problem having 30 of them opened at the end of the month.

Any suggestions on why this isn't work would be greatly appreciated.

J.

Some functions won't work with closed workbooks. =countif() is one of them.

But you can replace each of your =countif()'s with =sumproduct().

=SUMPRODUCT(--('c:\[aaaa.xls]bbbb'!B2:B41="whatever you want here"))

could be a replacement.




Egon wrote:

I'm having a problem with Excel not updating references in a countif
formula to another workbook. Other spreadsheets are working fine, but
this one I have isn't updating properly.

Automatic calculation is turned on and even doing a force with F9
doesn't make any difference. The only way I can make it pull in the
values from the other workbook is if I open those other work books.
This isn't going to be a problem for another week, but being that they
are doing a calculation based on a workbook that is created daily its
going to be a problem having 30 of them opened at the end of the month.

Any suggestions on why this isn't work would be greatly appreciated.

J.

--

Dave Peterson

Thanks a ton. I'll update it first thing in the AM and see if it works.

So SUMPRODUCT will work exactly as COUNTIF does?

Well, I tried your suggestion of using SUMPRODUCT and it doesn't do the
same thing as count if, unless I'm not understanding how you have the
formula written...

what's the "(--(" piece?

J.

=sumproduct() likes to work with numbers. the -- changes trues and falses to
+1's and 0's.

I'd guess that there was something wrong with your formula.

You may want to post your =countif() (with the other workbook open) and your
=sumproduct() version, too.



Egon wrote:

Well, I tried your suggestion of using SUMPRODUCT and it doesn't do the
same thing as count if, unless I'm not understanding how you have the
formula written...

what's the "(--(" piece?

J.

--

Dave Peterson

here is my countif statement

=COUNTIF('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul 05.xls]DMR'!$K$128:$N$137,
"Fugro")

The Sumproduct I used was

=SUMPRODUCT('S:\Major Projects\6917 Gomez\revenue and cost
tracking\vessel repoerts\[PS-MR-RP-DMR 01 JUL
05.xls]DMR'!$K$128:$N$137="Fugro")

I don't think the Sumproducts is right, but I'm not sure that I'm sure
how it should be.

Thanks for you help.
J

Ok, I have the following as my SUMPRODUCTS function:

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$254="Fugro"))

And I'm runing 3 of these on different parts of a workbook. 3 Different
parts of the same workbook. But the problem is the 3rd time its doing
it, it refused to look at the unopened workbook.

Any suggestions?

J.

I am having troubles updating SUMIF formulas when the link workbooks are
closed. It does work when I open them.

Any idea how to resolve


--
Roberto Serrano
------------------------------------------------------------------------
Roberto Serrano's Profile: http://www.excelforum.com/member.php...o&userid=25263
View this thread: http://www.excelforum.com/showthread...hreadid=384988

Saved from a previous post, but a more specific example:

The bad news is =sumif() won't work when you close your other workbook.

You could use =sumproduct() instead, though.

=SUMPRODUCT(--('[Dynamics Open Order Report.XLS]Dyn stock'!$A$4:$A$64998=$P2),
'[Dynamics Open Order Report.XLS]Dyn stock'!$E$4:$E64998)

Build the formula with that workbook open. Then you can verify that it works.
When you close the workbook, excel will add the path and folder.

In fact, I'd let excel do the work...

type
=sumproduct(--(

and point to A4:A64998 of the dyn stock worksheet.

(Do you really need all those rows? It could take a while to recalc.)



Roberto Serrano wrote:

I am having troubles updating SUMIF formulas when the link workbooks are
closed. It does work when I open them.

Any idea how to resolve

--
Roberto Serrano
------------------------------------------------------------------------
Roberto Serrano's Profile: http://www.excelforum.com/member.php...o&userid=25263
View this thread: http://www.excelforum.com/showthread...hreadid=384988

--

Dave Peterson