Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show top five records based on meeting multiple criteria | Excel Worksheet Functions | |||
count records which meet filter criteria | Excel Discussion (Misc queries) | |||
Counting unique records with additional criteria | Excel Worksheet Functions | |||
Filter Count of Records Retrieved. | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |