count records meeting three criteria
 

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.

count records meeting three criteria
 

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.

count records meeting three criteria
 

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.

count records meeting three criteria
 

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.

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.

count records meeting three criteria
 

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.