Counting cells between 2 values

  • Thread starter Thread starter abxy
  • Start date Start date
A

abxy

Basically, i'm trying to count the number of cells that have date
between 1/1/04 and 1/8/04, but all the cells that are being counted ar
in one colunm in another workbook. So i'm at a loss of how I can d
this...Any help
 
=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)
 
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
 
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?
Click to expand...

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
 
it's not working
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 ...
swore I saw that appear as the 3rd reply in this thread, I'll try ou
you all's suggestions right now...hopefully they'll work. Frank Kabe
is always right on the money
 
ok, Frank Kabel's answer works, but the only thing that I didn't mentio
was that i'm not just looking through Sheet1, it's more like Sheet
through Sheet30.

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

What am I to do
 
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
 
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 fil
'Febuary.xls' is closed, it will still work
 
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.
Click to expand...

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.
Click to expand...

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/[email protected]
 
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?
Click to expand...

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
 
for some reason that add-in isn't working right, maybe i'm doin
something wrong, I"ll give it a second looking over in a minute.

In the meantime, is there anytype of VBA alternative to this?

I mean, all it is that I'm trying to do is create weekly report. Th
workbook, is named after the month(Febuary) and each worksheet with th
exception of the first and the last (named Top and Bottom) are days o
the month (named 01, 02,... ...30, 31). Each worksheet has dates o
them, always in the same column. I just want to know how many of th
dates are between 2/2/04 and 2/5/04 on all of workbsheets between To
and Bottom.

Surely there must be a way to do this. Or some type of alternativ
approach to getting my results. any help?

It doesn't seem that complex, and yet it is..
 
Hi
If you like send me a sample of one of your workbooks with a detailed
description what you want to achieve and maybe there is an easier
solution.

I'm still not sure, what kind of #weekly' report you want to create.
Why do you want to access all worksheets within one workbook if each
workbook represents only 1 day of your month

Frank
 
oh, sorry, typo. Each workbook represents one month, each worksheet i
that workbook represents one day in that month
 
abxy > said:
for some reason that add-in isn't working right, maybe i'm doing
something wrong, I"ll give it a second looking over in a minute.
Click to expand...

I'll assume you mean the THREED function in MOREFUNC.XLL isn't working. It
won't if you're tyring to pull data from closed workbooks.
In the meantime, is there anytype of VBA alternative to this?
Click to expand...

FTHOI, I've put a Zip file containing some files with a minimal example of
one approach on my ftp space.

ftp://members.aol.com/hrlngrv/apull.zip

There are two .XLS workbooks, a VBA source code (.BAS) file that needs to be
imported into one of the two .XLS workbooks or into an open .XLA add-in, and
a README file basically repeating this paragraph.

It's slow, but it does get the job done. There's no other alternative of
which I'm aware.
I mean, all it is that I'm trying to do is create weekly report. The
workbook, is named after the month(Febuary) and each worksheet with the
exception of the first and the last (named Top and Bottom) are days of
the month (named 01, 02,... ...30, 31). Each worksheet has dates on
them, always in the same column. I just want to know how many of the
dates are between 2/2/04 and 2/5/04 on all of workbsheets between Top
and Bottom.
Click to expand...

Excel isn't too swift when it comes to 3D processing. With the exceptions of
MODE and CONCATENATE, only the built-in worksheet functions that accept
variable number of arguments also accept 3D references. No others do. Not
SUMIF, not COUNTIF, not SUMPRODUCT. Also, 3D references can't be used as
operands, so 'SheetX:SheetY'!A1:Z99+0 will only give you errors.

That means that Excel provides *NO* built-in way for you to could the number
of cells in a 3D reference meat *ANY* criteria. Not even if the 3D reference
were in the same workbook as the cell formula referring to it. This is a
major pain, but that's Excel for you.

[Since I thoroughly enjoy pointing out how other spreadsheet can do things
Excel can't, recent versions of 123 can handle this with @@ and @SUMIF.]
Surely there must be a way to do this. Or some type of alternative
approach to getting my results. any help?
Click to expand...

There is, but it takes programming. See above.
It doesn't seem that complex, and yet it is...
Click to expand...

Yes. This *IS* Excel we're discussing. Until its 3D functionality is brought
kicking & screaming into the 1990s(!), 3D blocks will continue to be a royal
pain to process.
 
Back
Top