Good Morning Excel Users,

Sorry about the title, what I need is confusing even me.

So I have a table listing people who receive services from my company, their names are listed in Column A.

They are considered Active when a start date is entered in Column B (Start Date) and are active until a date is entered in Column C (End Date), which is less than 60 days from the start date. There are a few who sign up for the service and decide against it almost immediately, and we identify those people by putting a Yes in Column D. The people with the yes in Column D should NOT be counted as Active. I've copied a bit of the table below, and would be happy to upload a copy if needed.

I need to know how many people are active on a given date and how many people were active during a month.

Where I am coming up with an issue is that the formulas I've tried don't seem to capture the people who have started before the date I am searching for when they don't have an end date, or an end date that falls after the date I am trying to capture. Which it should. I need to know that on 7/1/2014 I had 3 people active, just like I need to know that in the month of July, I had 4 people active during the month.

FYI: in the following examples, I replaced the cell B22 with the date listed in the cell, 7/1/2014.

I've tried a SUMPRODUCT((Table1[Start Date]>=7/1/2014)*(Table1[End Date]<=7/1/2014)*(Table1[Failed Start]<>"yes")), which only listed 2 people.


I've tried adding a column with a value of 1 (I called it column1) and using a sumif =SUMIFS(Table1[Column1],Table1[Failed Start],"<>YES",Table1[Start Date],"<="&7/1/2014,Table1[End Date],">="&7/1/2014)

I've tried and tried and now I'm just ready to chuck my computer out the window. Please help me save this poor computer from such a fate.

Any guidance/assitance would be most appreciated.

Last Name Start Date End Date Failed Start
Jane 08/20/13 10/18/13
John 10/18/13 12/15/13
Harry 01/26/14 03/21/14
Peter 03/21/14 05/14/14
Harriet 04/20/14 04/21/14 Yes
Donna 06/27/14
Brad 04/28/14 04/29/14
Sam 05/01/14
Philip 06/16/14 06/17/14 Yes
Kate 07/15/14
Charles 07/01/14