count records meeting three criteria

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[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

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

appreciated.

 

[Guest]

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.

 

[skaggsj]

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.