Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count occurances within a date...
Ok so I posted this question before but for some reason I could not get it to
work. What I need is this: Database ID-"dschnabe" can only have 5 "call id" at a time. Call ID Assigned Resolved Database ID 755669 05/27/08 05/28/08 dschnabe 754776 05/22/08 05/22/08 dschnabe 751502 05/09/08 05/09/08 dschnabe 747345 04/21/08 04/21/08 dschnabe 746533 04/16/08 04/18/08 dschnabe 746472 04/16/08 04/17/08 dschnabe 745730 04/14/08 04/15/08 dschnabe 741597 03/26/08 03/27/08 dschnabe 738424 03/12/08 03/13/08 dschnabe 738441 03/12/08 03/18/08 dschnabe 737777 03/10/08 03/11/08 dschnabe 737773 03/10/08 03/11/08 dschnabe 737271 03/07/08 03/10/08 dschnabe 737253 03/07/08 03/07/08 dschnabe 283748 03/07/08 03/07/08 dschnabe 847592 03/07/08 03/07/08 dschnabe 948572 03/07/08 03/07/08 dschnabe 348478 03/07/08 03/07/08 dschnabe In this case at the end dschnabe has 6 call id's on 03/07/08 so that should give me and "Error" message. How can I count this? I need to know for sure this guy only works on 5 call id's or less at a time, if not i need to know,HELP!!! In my table there are many database id's and call id's so I not only need this to work for "dschnabe" but for the other 100 users or so. -- sss |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count occurances within a date...
Maybe something like this:
=IF(SUMPRODUCT(--(B2:B19=K1),--(D2:D19=D2))5,"Help!",SUMPRODUCT(--(B2:B19=K1),--(D2:D19=D2))) K1 represents a cell with the date that you want a count from. In this formula, if the count matching the criteria of the Database ID and Assigned Date is greater than 5, then "Help!" is returned. Otherwise, it just returns the count of matching data (less than 6, of course). Does that help? Regards, Paul -- "sed" wrote in message ... Ok so I posted this question before but for some reason I could not get it to work. What I need is this: Database ID-"dschnabe" can only have 5 "call id" at a time. Call ID Assigned Resolved Database ID 755669 05/27/08 05/28/08 dschnabe 754776 05/22/08 05/22/08 dschnabe 751502 05/09/08 05/09/08 dschnabe 747345 04/21/08 04/21/08 dschnabe 746533 04/16/08 04/18/08 dschnabe 746472 04/16/08 04/17/08 dschnabe 745730 04/14/08 04/15/08 dschnabe 741597 03/26/08 03/27/08 dschnabe 738424 03/12/08 03/13/08 dschnabe 738441 03/12/08 03/18/08 dschnabe 737777 03/10/08 03/11/08 dschnabe 737773 03/10/08 03/11/08 dschnabe 737271 03/07/08 03/10/08 dschnabe 737253 03/07/08 03/07/08 dschnabe 283748 03/07/08 03/07/08 dschnabe 847592 03/07/08 03/07/08 dschnabe 948572 03/07/08 03/07/08 dschnabe 348478 03/07/08 03/07/08 dschnabe In this case at the end dschnabe has 6 call id's on 03/07/08 so that should give me and "Error" message. How can I count this? I need to know for sure this guy only works on 5 call id's or less at a time, if not i need to know,HELP!!! In my table there are many database id's and call id's so I not only need this to work for "dschnabe" but for the other 100 users or so. -- sss |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count occurances within a date...
Hi,
Maybe the easiest way to do this is by putting the two values you want to check for in 2 cells and then use a formula like: =SUMPRODUCT((C2:C19=$G$2)*(D2:D19=$H$2)) In this example C2:C19 contain the dates and the date you want to check for is in G2, D2:D19 contains the text entry you want to check for and its value is entered in H2. This formula simply returns the count of the number of items that meet both conditions. If you want to display a message in the cell you could add: =SUMPRODUCT((C2:C19=$G$2)*(D2:D19=$H$2))5 this would return TRUE or FALSE instead of the count. or =IF(SUMPRODUCT((C2:C19=$G$2)*(D2:D19=$H$2))5,"We have a problem, Houston","") There are many additional modifications one could do. -- Cheers, Shane Devenshire Microsoft Excel MVP "sed" wrote: Ok so I posted this question before but for some reason I could not get it to work. What I need is this: Database ID-"dschnabe" can only have 5 "call id" at a time. Call ID Assigned Resolved Database ID 755669 05/27/08 05/28/08 dschnabe 754776 05/22/08 05/22/08 dschnabe 751502 05/09/08 05/09/08 dschnabe 747345 04/21/08 04/21/08 dschnabe 746533 04/16/08 04/18/08 dschnabe 746472 04/16/08 04/17/08 dschnabe 745730 04/14/08 04/15/08 dschnabe 741597 03/26/08 03/27/08 dschnabe 738424 03/12/08 03/13/08 dschnabe 738441 03/12/08 03/18/08 dschnabe 737777 03/10/08 03/11/08 dschnabe 737773 03/10/08 03/11/08 dschnabe 737271 03/07/08 03/10/08 dschnabe 737253 03/07/08 03/07/08 dschnabe 283748 03/07/08 03/07/08 dschnabe 847592 03/07/08 03/07/08 dschnabe 948572 03/07/08 03/07/08 dschnabe 348478 03/07/08 03/07/08 dschnabe In this case at the end dschnabe has 6 call id's on 03/07/08 so that should give me and "Error" message. How can I count this? I need to know for sure this guy only works on 5 call id's or less at a time, if not i need to know,HELP!!! In my table there are many database id's and call id's so I not only need this to work for "dschnabe" but for the other 100 users or so. -- sss |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count of different occurances | Excel Discussion (Misc queries) | |||
count occurances | Excel Discussion (Misc queries) | |||
Count occurances within a date range | Excel Discussion (Misc queries) | |||
count date occurances in range of dates... | Excel Worksheet Functions | |||
Count the occurances of a month in a range of date fields | Excel Worksheet Functions |