count records meeting three criteria

[Laura]

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.

[Ashish Mathur]

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.

[Laura]

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.

[Peo Sjoblom]

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" <Laura@discussions.microsoft.com> wrote in message
news:9FB87ECB-C96A-47C8-81DB-F5F8CC8C044B@microsoft.com...
> 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.

[Laura]

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" <Laura@discussions.microsoft.com> wrote in message
> news:9FB87ECB-C96A-47C8-81DB-F5F8CC8C044B@microsoft.com...
> > 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.
>
>
>

[skaggsj@gmail.com]

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.