I got it to work!! To get it to count between 5 and 10 I used:
=SUMPRODUCT(--('C:\Documents\excel\[Book1.xls]Sheet3'!$B$1:B$30>=5),--('C:\Documents\excel\[Book1.xls]Sheet3'!$B$1:B$30<=10))
Thanks Dave for getting me on the right track.
Mike


"Mike" <windme@cox.net> wrote in message
news:1Hxge.9128$Ay3.3854@lakeread06...
> Thanks Dave for your help. I changed the =countif to the =sumproduct
> formula but it doesn't seem to work. It doesn't give me the #VALUE! but
> it didn't calculate it corrrectly. I'm not sure what you mean by the
> whole column, like an A:A formula? I want it to count the number of
> occurences in $B$1:$B$30.
> Also what would I use if i wanted it to find occurences between two
> numbers, say 5 & 10. Would that still be a SUMPRODUCT formula?
> TIA,
> Mike
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:42828A47.A3C4DABA@netscapeXSPAM.com...
>> But there are equivalent formulas:
>>
>> this formula:
>> =COUNTIF('C:\My Documents\excel\[Book1.xls]Sheet 3'!$A$1:$A$13,"asdf")
>> breaks when I close book1.xls.
>>
>> But this formula will continue to work:
>> =SUMPRODUCT(--('C:\My Documents\excel\[Book1.xls]Sheet
>> 3'!$A$1:$A$13="asdf"))
>>
>> (Except that you can't use the whole column with =sumproduct().)
>>
>> Mike wrote:
>>>
>>> Thanks Andy for your help. In the workbook that displays the data I
>>> have
>>> two functions, =SUM and =AVERAGE. In the workbook that will not update
>>> they
>>> all contain =COUNTIF so mabye that is one of the functions that you have
>>> to
>>> have the workbook link open to see the data.
>>> Mike
>>> <Andy B> wrote in message
>>> news:%23I5KhljVFHA.3184@TK2MSFTNGP15.phx.gbl...
>>> > Hi
>>> >
>>> > It's not a very precise answer but it might give you a clue and send
>>> > you
>>> > off looking in the right direction!
>>> > Certain functions (I believe INDIRECT is one) will not work on closed
>>> > workbooks. The workbooks must be open for the function to operate.
>>> > I'll
>>> > see if I can find more details for you - but it's a start!
>>> > Hope this helps!
>>> >
>>> > --
>>> > Andy.
>>> >
>>> >
>>> > "Mike" <windme@cox.net> wrote in message
>>> > news:c%nge.5987$Ay3.4021@lakeread06...
>>> >> Good Morning All,
>>> >> Using Excel 2002
>>> >>
>>> >> I have several workbooks in which all data in cells come
>>> >> from
>>> >> links of other workbooks which are all located in "My Documents".
>>> >> When
>>> >> I open both workbooks I get the message box about the workbook
>>> >> containing
>>> >> links to other data sources. When I click on to update the data the
>>> >> first workbook takes a few seconds to update all the data and opens
>>> >> up
>>> >> the workbook with all the data readable. However in the 2nd
>>> >> workbook,
>>> >> after several seconds, it opens but none of the data is displayed.
>>> >> All
>>> >> the cells have the #VALUE! error. This forces me to open up the
>>> >> source
>>> >> workbook(s) to display the data.
>>> >> I am trying to figure out why the first workbook opens
>>> >> with
>>> >> all the updated data (without the source workbook(s) opening) while
>>> >> the
>>> >> 2nd workbook does not update the data unless I open up the source
>>> >> workbook(s). I would like to update all information without having to
>>> >> open the source workbook(s). Any help would be appreciated. Thanks.
>>> >>
>>> >> Mike
>>> >>
>>> >>
>>> >
>>> >
>>
>> --
>>
>> Dave Peterson
>
>