count records meeting three criteria
It isn't working. With ="="&B1, it just returns 0. (The correct number in
this case should be 11.)
Here is more information:
B1 =12/1/2005
C1 =12/19/2005
A58 =DCOUNTA(Cases!$A$1:$G$897,"Case Number",A53:C54)
A53 Order {field name}
A54 ="=Notice" {value in field Order}
B53 Order Date {field name}
B54 ="="&B1
C53 Order Date {field name}
C54 ="<="&C1
This works if I use:
B54 =12/1/2005
C54 <=12/19/2005
Thanks for your help! I am completely baffled by why this isn't working.
"Peo Sjoblom" wrote:
Use
="="&B1
and
="<="&C1
note that it will display the dates serial number in the cell itself (number
of days since Jan 0 1900) which may confuse your users so you can insert the
text function as well
="="&TEXT(B1,"mm/dd/yy")
and
="<="&TEXT(C1,"mm/dd/yy")
--
Regards,
Peo Sjoblom
"Laura" wrote in message
...
Let me clarify:
The data is in range A4:G400. I want to be able to put two dates at the
top
of the spreadsheet in cells B1 and C1 to use in my query. (Basically, I
want
to make it easy for an end-user to just type in the dates in one spot to
be
used in several separate formulas that use DCOUNTA.)
"Ashish Mathur" wrote:
Hi,
You may try the following array formula (Ctrl+Shift+Enter). The data is
in
range A2:B4
12/12/1991 12
15/01/1992 13
21/01/1992 12
=SUM(IF((A2:A4=A2)*(A2:A4<=A4),B2:B4))
"Laura" wrote:
I am using DCOUNTA to count "records" (rows) in an Excel spreadsheet
using
three criteria:
Type ="=Order"
Date =12/1/2005
Date <=12/19/2005
This works fine; however, I want to be able to use a reference to a
cell
containing the date, rather than having to enter the date in the
DCOUNT
criteria cells. Something like:
Type ="=Order"
Date =$B$1
Date <=$C$1
where B1 contains the start date and C1 contains the end date.
It works if I use Date =$B$1, but it won't do greater than or equal to
$B$1.
Suggestions using DCOUNT or any other method would be greatly
appreciated.
|