Counting how many rows fall on a certain date between two other dates in different cells

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

Try this..

Formula:

Please Login or Register  to view this content.

Don't forget to Click *

Hi Vikas, thanks for responding. Unfortunately, that formula returned a value of 0. I'd like it to count 3 (Charles, Sam and Donna) as the people active on 7/1/2014, and when I run the formula for the people active in July, I'd like it to pick up 4 (those three people and Kate).

I hope that helps clarify the issue.

I got the problem..
the problem is in the date formatting..
you can see its.. mm/dd/yyyy but in our formula its dd/mm/yyyy

thats why its not working up..

Now first, try this

Formula:

Please Login or Register  to view this content.

and if it does not work...
then use a cell reference instead of datevalue("01/07/2014") by putting a the date in that cell...

assuming you will you F1 then formula would be..

Formula:

Please Login or Register  to view this content.

or try this...

Formula:

Please Login or Register  to view this content.

Don't forget to click *

I appreciate your assistance! You really helped me out. Have a great day!