Kind of advanced formula question?

[Guest]

In my worksheet I have the name of a paitent, the date they were ENROLLED in
our program, the date they were DISENROLLED from our program and the person
who signed them up.

My boss is only concerned with seeing numbers for them if they were enrolled
for less than 60 days.

I've gotten help for totalling the # of enrolled people per person that
signed them up, but what I actually need to do is only count them if they've
been in the program <60 days, i.e.

Client Name: Signup Date: Quit Date: Counselor
Janet 10-24-07 10-27-07 Mike
Bill 10-24-07 02-15-08 Mike
Evan 10-26-07 11-01-07 Steve
Lucy 10-27-07 06-05-10 Alice

In this example I'd need to see that Mike has ONE "quitter" and Steve has
one as well. So is it possible to make a formula that basically says:

If Col C - Col B = <60 THEN Mike += 1

or something?

Thanks for looking!

 

[Bernard Liengme]

=SUMPRODUCT(--(C1:C100-B1:B100>60),--(D1100="Mike"))
best wishes

 

[iliace]

List all your counselors in a separate section of the sheet. Let's
say those are columns A through D, then list them in column F,
starting at row 2 (save row 1 for headers).

Next to each name, in column G, your formula is:

=SUMPRODUCT(--($D$2:$D$5=$F2),--($C$2:$C$5-$B$2:$B$5<=60),--($C$2:$C$5-
$B$2:$B$5>0))

Replace the references as appropriate (assuming your list is more than
4 entries). Copy down next to each counselor's name. Note that this
formula assumes that clients who are still in program have a blank
next to quit date, and does not count them (last condition). Might
want to use auto-expanding named ranges for when you add people later
to be included in the count automatically.


Also, this is my first time ever posting a non-array SUMPRODUCT
solution. I think I'll go celebrate.

 

[Guest]

Thanks Bernard. Works!

=SUMPRODUCT(--(C1:C100-B1:B100>60),--(D1100="Mike"))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email