Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kind of advanced formula question?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kind of advanced formula question?
=SUMPRODUCT(--(C1:C100-B1:B10060),--(D1:D100="Mike"))
best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Angyl" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kind of advanced formula question?
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$50)) 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. On Oct 30, 3:22 pm, Angyl wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kind of advanced formula question?
Thanks Bernard. Works!
"Bernard Liengme" wrote: =SUMPRODUCT(--(C1:C100-B1:B10060),--(D1:D100="Mike")) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Angyl" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A kind of auto-filter question... | Excel Worksheet Functions | |||
sumproduct question - kind of... | Excel Worksheet Functions | |||
Advanced formula/inserting text question | Excel Worksheet Functions | |||
Pivot Table Question - KIND of UrgentTh | Excel Discussion (Misc queries) | |||
Help what kind of formula? | New Users to Excel |