Counting cells between 2 values

Basically, i'm trying to count the number of cells that have dates
between 1/1/04 and 1/8/04, but all the cells that are being counted are
in one colunm in another workbook. So i'm at a loss of how I can do
this...Any help?


---
Message posted from http://www.ExcelForum.com/

=SUMPRODUCT(([Book2]Sheet1!$A$1:$A$10=DATE(2004,1,1))*([Book2]Sheet1!$A$1:$
A$10=DATE(2004,8,1)))

By the way, I am assuming UK style dates, so 1/8/04 is 1st August?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"abxy " wrote in message
...
Basically, i'm trying to count the number of cells that have dates
between 1/1/04 and 1/8/04, but all the cells that are being counted are
in one colunm in another workbook. So i'm at a loss of how I can do
this...Any help?


---
Message posted from http://www.ExcelForum.com/

Try:

=COUNTIF([Book5]Sheet1!$A:$A,"=1/1/04")-COUNTIF([Book5]
Sheet1!$A:$A,"1/8/04")

Change the wb name, sheet name, and col. reference if
needed. Format the formula sign as number.

HTH
Jason
Atlanta, GA

-----Original Message-----
Basically, i'm trying to count the number of cells that
have dates
between 1/1/04 and 1/8/04, but all the cells that are
being counted are
in one colunm in another workbook. So i'm at a loss of
how I can do
this...Any help?


---
Message posted from http://www.ExcelForum.com/

.

Basically, i'm trying to count the number of cells that have dates
between 1/1/04 and 1/8/04, but all the cells that are being counted
are in one colunm in another workbook. So i'm at a loss of how I can
do this...Any help?


---
Message posted from http://www.ExcelForum.com/

Hi
try
=SUMPRODUCT(('[Book1.xls]Sheet1'!$A$1:$A$999=DATE(2004,1,1))*('[Book1.
xls]Sheet1'!$A$1:$A$999=DATE(2004,1,8)))

counts your entries between January 1st and January 8th

Frank

"abxy " wrote...
it's not working

What's your *EXACT* formula?

--
To top-post is human, to bottom-post and snip is sublime.

it's not working


---
Message posted from http://www.ExcelForum.com/

Hi
a little bit more information would be helpful :-)
- did the formula return an error?
- how did you adapt the formual (change the workbook name, etc.)
- is the other workbook closeD (if yes, you have to include the
pathname for the reference / try opening the other workbook and see if
you get the results you want)
- have you changed our assumed range

Frank

i'm sorry, i wrote "it's not working" after the first 2 replies ...I
swore I saw that appear as the 3rd reply in this thread, I'll try out
you all's suggestions right now...hopefully they'll work. Frank Kabel
is always right on the money.


---
Message posted from http://www.ExcelForum.com/

Hi
AFAIK SUMPRODUCT is not able to process 3D arrays. One way could be the
use of the Add-in MOREFUNC.XLL (http://longre.free.fr/english). The
implemented function THREED converts a 3D array to a 2D array:
Your formula would look like:
=SUMPRODUCT((THREED('[Book1.xls]Sheet1:sheet30'!$A$1:$A$999)=DATE(2004
,1,1))*(THREED('[Book1.
xls]Sheet1:sheet30'!$A$1:$A$999)=DATE(2004,1,8)))

Though this will work only, if the wokbook 'book1.xls' is opened!. If
its closed, the above formula will return an erro (#REF)

Frank



ok, Frank Kabel's answer works, but the only thing that I didn't
mention was that i'm not just looking through Sheet1, it's more like
Sheet1 through Sheet30.

I know that putting something like: Sheet1:Sheet30 means
everything
between Sheet1 thru Sheet30 (in my case, "my Sheet1" is named Top and
"my Sheet30" is named Bottom) but when I put in "Top:Bottom'!" into
the formula, it doesn't work.

What am I to do?

"abxy " wrote...
ok, Frank Kabel's answer works, but the only thing that I didn't mention
was that i'm not just looking through Sheet1, it's more like Sheet1
through Sheet30.

All the other respondents' formulas also work because (no big surprise) they're
all basically the same.

I know that putting something like: Sheet1:Sheet30 means everything
between Sheet1 thru Sheet30 (in my case, "my Sheet1" is named Top and
"my Sheet30" is named Bottom) but when I put in "Top:Bottom'!" into the
formula, it doesn't work.

You can't use 3D references in this situation. You need to create a list of the
worksheet names to be processed. Best (as in most easily maintained) to enter
such a list in a 30-row by 1-column range and give that range a name like WSLST.

Then you need to use trickery.

=SUMPRODUCT((N(OFFSET(INDIRECT("'"&WSLST&"'!A1"),
{0;1;2;3;4;5;6;7;8;9},0,1,1))=DATEVALUE("1/1/2004"))
*(N(OFFSET(INDIRECT("'"&WSLST&"'!A1"),
{0;1;2;3;4;5;6;7;8;9},0,1,1))=DATEVALUE("1/8/2004")))

If the references would be to another workbook, then add the workbook's name.

=SUMPRODUCT((N(OFFSET(INDIRECT("'[workbook.xls]"&WSLST&"'!A1"),
{0;1;2;3;4;5;6;7;8;9},0,1,1))=DATEVALUE("1/1/2004"))
*(N(OFFSET(INDIRECT("'[workbook.xls]"&WSLST&"'!A1"),
{0;1;2;3;4;5;6;7;8;9},0,1,1))=DATEVALUE("1/8/2004")))

This requires the other workbook to be open. If the other workbook would be
closed, there's no easy way to do this. All I can think of would involve calling
a user-defined function that in turn calls the udf in the following linked
article.

http://www.google.com/groups?selm=sH...wsranger. com

--
To top-post is human, to bottom-post and snip is sublime.

wait, let me get this straight. it'll only work if the default
workbook, 'Book1.xsl' is open, meaning that it'll still work if my
file 'Febuary.xls' is closed, it will still work?


---
Message posted from http://www.ExcelForum.com/

Hi
I do not know what file 'february.xls' is - you ddidn't mention this
workbook before??
The important thing is, taht the referenced workbookname in the formula
=SUMPRODUCT((THREED('[Book1.xls]Sheet1:sheet30'!$A$1:$A$999)=DATE(2004
,1,1))*(THREED('[Book1.
xls]Sheet1:sheet30'!$A$1:$A$999)=DATE(2004,1,8)))

has to be opened. In this case I used the dummy name 'book1.xls' for
this workbook. So you have to open the workbook which contains the
values to count.
Just try it by yourself.

Frank