EXCEL - count per timeframe, without each one listed 4

[Jeanie]

I have a spreadsheet that has several columns of data, one being a "start date" and one being an "end date". I want to be able to take look at this data and say how many records were eligible for a specific month, without having to list each month. Below is a small sample:

12345 Smith 1/1/2006 12/31/2006
23456 Jones 1/1/2006 11/30/2006
34567 Taylor 3/1/2006 11/30/2006

Is there some kind of a formula I could use to count how many were eligible in January 2006 (2), or December 2006 (1), or February 2006 (2), or May 2006 (3), etc.?

Any help would be appreciated!

Thanks,

Jeanie

 

[SkipVought]

Hi,

[tt]
=sumproduct(--(Start_Date<=MyDate)*(End_Date>=MyDate))
[/tt]
where Start_Date & End_Date are named ranges and MyDate (also a named range for one cell) has a real date.

Skip,

_[red][/red]

 

[Jeanie]

Thanks! I love this site and all you brilliant people...I don't come out here often but ALWAYS get a quick response - and it usually works....as this one did.

Thanks again!

Jeanie

 

[Jeanie]

One more question on this. How would I modify the formula to account for blanks in the End_Date range? Some of the records do not yet have an end date, but need to still be counted.

Thanks,

Jeanie

 

[SkipVought]

[tt]
=SUMPRODUCT(--(Start_date<=MyDate)*OR((End_Date>=MyDate),(End_Date=0)))
[/tt]


Skip,

_[red][/red]

 

[xlhelp]

Skip

Can't help but admire your depth of knowledge in every post you reply to.

Member- AAAA Association Against Acronym Abusers

 

[Jeanie]

That works great! For the ease of my users I am wanting to make the range for both the start date and end date 100s of lines longer than the records we currently have (so they can add to it and not worry about the formula). When I expand the range to include these totally blank records, it counts all of them that do NOT have an end date. Is there a way to fix this?

I really appreciate you help with this!

Thanks,

Jeanie

 

[Rofeu]

to build upon Skip's formula:

=SUMPRODUCT(--(Start_date<=MyDate)*OR((End_Date>=MyDate),(End_Date=0))*--(Start_date>0))

Cheers,

Roel

 

[SkipVought]

xlhelp,

Thanks for the kudos, but...

it is ALSO the way I feel when I see other great postings, like many of yours, that make me say, "Wow! I didn't know that!"

There's ALWAYS opportunity to learn something new, and Tek-Tips is the premere place for this kind of learning for me!

Skip,

_[red][/red]

 

[Jeanie]

Thanks both Skip and Rofeu - this works perfectly!!

Jeanie

 

[Jeanie]

Oops....in further review I find it is not counting how I want it to. Below is an example:

File # Start Date End Date
12345 4/24/2006 7/31/2006
23456 6/23/2006 9/30/2006
34567 8/4/2006 8/31/2006
45678 9/13/2006 9/30/2006

Should count the following:

April 0
May 1
June 1
July 2
August 1
September 1
October 0

But is counting as follows:

April 0
May 1
June 1
July 2
August 2
September 3
October 4

The formula I have is: SUMPRODUCT(--(Start_Date<=A4)*OR((End_Date>=A4),(End_Date=0))*--(Start_Date>0))

A4 is the cell referencing the month I am counting for.

Thanks,

Jeanie

 

[Rofeu]

use this:

=SUMPRODUCT(--(Start_Date<=RC1)*--((End_Date>=RC1)+(End_Date=0))*--(Start_Date>0))

Cheers,

Roel

 

[SkipVought]

That's because of...
[tt]
--(Start_Date>0)
[/tt]


Skip,

_[red][/red]

 

[Rofeu]

It's not, Skip, it's because of the OR function, that doesn't work with sumproduct.

Using * gives you the equivalent of AND
Using + gives you the equivalent of OR

Cheers,

Roel

 

[SkipVought]

Roel,

Gotta hand that one to you! ==> *

Skip,

_[red][/red]

 

[Jeanie]

Thanks again - I think that it works exactly as I hoped now!

Jeanie

 

[Rofeu]

Thanx for the star, Skip. I never thought I'd see the day... ;-)

Cheers,

Roel

 

[SkipVought]

Roel,

You taught me something I did not know. I appreciate that.

Skip,

_[red][/red]