Count occurances within a date...

[sed]

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.

 

[PCLIVE]

Maybe something like this:

=IF(SUMPRODUCT(--(B2:B19=K1),--(D219=D2))>5,"Help!",SUMPRODUCT(--(B2:B19=K1),--(D219=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

 

[ShaneDevenshire]

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)*(D219=$H$2))

In this example C2:C19 contain the dates and the date you want to check for
is in G2, D219 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)*(D219=$H$2))>5

this would return TRUE or FALSE instead of the count.

or
=IF(SUMPRODUCT((C2:C19=$G$2)*(D219=$H$2))>5,"We have a problem, Houston","")

There are many additional modifications one could do.