Formula Question

[Mike]

I have a spreadsheet that I'm working on that tracks employee
attendance. A sample is included below:

A B C D
1 Name 06/07/04 Final Warning Attendance
2 Name 08/21/03 Warning Tardy
3 Name 12/04/02 Warning Attendance
4 Name 09/16/02 Counsel Tardy
5 Name 08/05/02 Counsel Attendance

Is there a formula that will tell me how many instances an employee was
on an Attendance or Tardy disciplinary step of Final Warning, Warning or
Counsel based on a rolling year? The columns being compared are B, C
and D. I don't care about the employee's name. As an example, if I
have a huge list of people I want to know how many times the combination
of "Attendance" and "Warning" were applied during a rolling year. The
list is highly dynamic and constantly being updated.

Thanks for your help.

Mike

 

[Frank Kabel]

Hi
try something like
=SUMPRODUCT(--(B1:B100>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()
))),--(C1:C100="Warning"),--(D1100="Attendance"))

 

[Mike]

Frank,

Thank you for your kind assistance. As usual, you've come through for me.
I appreciate it.

Mike

 

[CLR]

Hi Mike..........

I know you asked for a formula, and Frank gave you one that you are happy
with, but another thing you might consider would be to use the AutoFilter
for an analysis like this.........you can set the date column to be Greater
than a certain date "and" Less than another date, for any range you wish and
then set up the other column filters as desired........it lets you "see" the
results rather than just a count of them..........

Vaya con Dios,
Chuck, CABGx3

 

[rod_inman]

would u be willing to share ur sheet? it would sur emake life easier fo
me.. right now i keep track of almost the exact same thing as u do, an
ur sheet would be of help to me.. than

 

[Mike]

Thanks Chuck... I'll give that a try too. The formula that Frank gave me does
the trick nicely, but your method may also be good to. I'll make up two
spreadsheets and show the boss and let her decide which is best for her needs.

Mike

 

[Mike]

Rod,

I sent you a sample of the spreadsheet via hotmail.

Mike