software

I am using DCOUNTA to count quot;recordsquot; (rows) in an Excel spreadsheet using
three criteria:
Type =quot;=Orderquot;
Date gt;=12/1/2005
Date lt;=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 =quot;=Orderquot;
Date gt;=$B$1
Date lt;=$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.

Hi,

You may try the following array formula (Ctrl Shift Enter). The data is in
range A2:B4

12/12/199112
15/01/199213
21/01/199212

=SUM(IF((A2:A4gt;=A2)*(A2:A4lt;=A4),B2:B4))

quot;Lauraquot; wrote:

gt; I am using DCOUNTA to count quot;recordsquot; (rows) in an Excel spreadsheet using
gt; three criteria:
gt; Type =quot;=Orderquot;
gt; Date gt;=12/1/2005
gt; Date lt;=12/19/2005
gt;
gt; This works fine; however, I want to be able to use a reference to a cell
gt; containing the date, rather than having to enter the date in the DCOUNT
gt; criteria cells. Something like:
gt; Type =quot;=Orderquot;
gt; Date gt;=$B$1
gt; Date lt;=$C$1
gt; where B1 contains the start date and C1 contains the end date.
gt;
gt; It works if I use Date =$B$1, but it won't do greater than or equal to $B$1.
gt;
gt; Suggestions using DCOUNT or any other method would be greatly appreciated.

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.)

quot;Ashish Mathurquot; wrote:

gt; Hi,
gt;
gt; You may try the following array formula (Ctrl Shift Enter). The data is in
gt; range A2:B4
gt;
gt; 12/12/199112
gt; 15/01/199213
gt; 21/01/199212
gt;
gt; =SUM(IF((A2:A4gt;=A2)*(A2:A4lt;=A4),B2:B4))
gt;
gt; quot;Lauraquot; wrote:
gt;
gt; gt; I am using DCOUNTA to count quot;recordsquot; (rows) in an Excel spreadsheet using
gt; gt; three criteria:
gt; gt; Type =quot;=Orderquot;
gt; gt; Date gt;=12/1/2005
gt; gt; Date lt;=12/19/2005
gt; gt;
gt; gt; This works fine; however, I want to be able to use a reference to a cell
gt; gt; containing the date, rather than having to enter the date in the DCOUNT
gt; gt; criteria cells. Something like:
gt; gt; Type =quot;=Orderquot;
gt; gt; Date gt;=$B$1
gt; gt; Date lt;=$C$1
gt; gt; where B1 contains the start date and C1 contains the end date.
gt; gt;
gt; gt; It works if I use Date =$B$1, but it won't do greater than or equal to $B$1.
gt; gt;
gt; gt; Suggestions using DCOUNT or any other method would be greatly appreciated.

Use

=quot;gt;=quot;amp;B1

and

=quot;lt;=quot;amp;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

=quot;gt;=quot;amp;TEXT(B1,quot;mm/dd/yyquot;)

and

=quot;lt;=quot;amp;TEXT(C1,quot;mm/dd/yyquot;)--

Regards,

Peo Sjoblom

quot;Lauraquot; gt; wrote in message
...
gt; Let me clarify:
gt; The data is in range A4:G400. I want to be able to put two dates at the
top
gt; of the spreadsheet in cells B1 and C1 to use in my query. (Basically, I
want
gt; to make it easy for an end-user to just type in the dates in one spot to
be
gt; used in several separate formulas that use DCOUNTA.)
gt;
gt; quot;Ashish Mathurquot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; You may try the following array formula (Ctrl Shift Enter). The data is
in
gt; gt; range A2:B4
gt; gt;
gt; gt; 12/12/1991 12
gt; gt; 15/01/1992 13
gt; gt; 21/01/1992 12
gt; gt;
gt; gt; =SUM(IF((A2:A4gt;=A2)*(A2:A4lt;=A4),B2:B4))
gt; gt;
gt; gt; quot;Lauraquot; wrote:
gt; gt;
gt; gt; gt; I am using DCOUNTA to count quot;recordsquot; (rows) in an Excel spreadsheet
using
gt; gt; gt; three criteria:
gt; gt; gt; Type =quot;=Orderquot;
gt; gt; gt; Date gt;=12/1/2005
gt; gt; gt; Date lt;=12/19/2005
gt; gt; gt;
gt; gt; gt; This works fine; however, I want to be able to use a reference to a
cell
gt; gt; gt; containing the date, rather than having to enter the date in the
DCOUNT
gt; gt; gt; criteria cells. Something like:
gt; gt; gt; Type =quot;=Orderquot;
gt; gt; gt; Date gt;=$B$1
gt; gt; gt; Date lt;=$C$1
gt; gt; gt; where B1 contains the start date and C1 contains the end date.
gt; gt; gt;
gt; gt; gt; It works if I use Date =$B$1, but it won't do greater than or equal to
$B$1.
gt; gt; gt;
gt; gt; gt; Suggestions using DCOUNT or any other method would be greatly
appreciated.
It isn't working. With =quot;gt;=quot;amp;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,quot;Case Numberquot;,A53:C54)
A53 Order {field name}
A54 =quot;=Noticequot; {value in field Order}
B53 Order Date {field name}
B54 =quot;gt;=quot;amp;B1
C53 Order Date {field name}
C54 =quot;lt;=quot;amp;C1

This works if I use:
B54 gt;=12/1/2005
C54 lt;=12/19/2005

Thanks for your help! I am completely baffled by why this isn't working.

quot;Peo Sjoblomquot; wrote:

gt; Use
gt;
gt; =quot;gt;=quot;amp;B1
gt;
gt; and
gt;
gt; =quot;lt;=quot;amp;C1
gt;
gt; note that it will display the dates serial number in the cell itself (number
gt; of days since Jan 0 1900) which may confuse your users so you can insert the
gt; text function as well
gt;
gt; =quot;gt;=quot;amp;TEXT(B1,quot;mm/dd/yyquot;)
gt;
gt; and
gt;
gt; =quot;lt;=quot;amp;TEXT(C1,quot;mm/dd/yyquot;)
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; quot;Lauraquot; gt; wrote in message
gt; ...
gt; gt; Let me clarify:
gt; gt; The data is in range A4:G400. I want to be able to put two dates at the
gt; top
gt; gt; of the spreadsheet in cells B1 and C1 to use in my query. (Basically, I
gt; want
gt; gt; to make it easy for an end-user to just type in the dates in one spot to
gt; be
gt; gt; used in several separate formulas that use DCOUNTA.)
gt; gt;
gt; gt; quot;Ashish Mathurquot; wrote:
gt; gt;
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; You may try the following array formula (Ctrl Shift Enter). The data is
gt; in
gt; gt; gt; range A2:B4
gt; gt; gt;
gt; gt; gt; 12/12/1991 12
gt; gt; gt; 15/01/1992 13
gt; gt; gt; 21/01/1992 12
gt; gt; gt;
gt; gt; gt; =SUM(IF((A2:A4gt;=A2)*(A2:A4lt;=A4),B2:B4))
gt; gt; gt;
gt; gt; gt; quot;Lauraquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am using DCOUNTA to count quot;recordsquot; (rows) in an Excel spreadsheet
gt; using
gt; gt; gt; gt; three criteria:
gt; gt; gt; gt; Type =quot;=Orderquot;
gt; gt; gt; gt; Date gt;=12/1/2005
gt; gt; gt; gt; Date lt;=12/19/2005
gt; gt; gt; gt;
gt; gt; gt; gt; This works fine; however, I want to be able to use a reference to a
gt; cell
gt; gt; gt; gt; containing the date, rather than having to enter the date in the
gt; DCOUNT
gt; gt; gt; gt; criteria cells. Something like:
gt; gt; gt; gt; Type =quot;=Orderquot;
gt; gt; gt; gt; Date gt;=$B$1
gt; gt; gt; gt; Date lt;=$C$1
gt; gt; gt; gt; where B1 contains the start date and C1 contains the end date.
gt; gt; gt; gt;
gt; gt; gt; gt; It works if I use Date =$B$1, but it won't do greater than or equal to
gt; $B$1.
gt; gt; gt; gt;
gt; gt; gt; gt; Suggestions using DCOUNT or any other method would be greatly
gt; appreciated.
gt;
gt;
gt;

In cells B1 and C1, it appears that you've typed in the equals sign
before the date. If you remove those, your formula should work fine.