Counting Within a Date Range

[Guest]

I'm trying to count the number of people present during any given date range
which I have been able to do. The problem I'm having is including people who
are still here. My simplified dataset looks like this

A B C
Sam 1-Oct-07 13-Oct-07
Joe 8-Oct-07 12-Oct-07
Julie 10-Oct-07
Steve 10-Oct-07 19-Oct-07
James 15-Oct-07 15-Oct-07
Mary 15-Oct-07 17-Oct-07
Sally 18-Oct-07
Juliet 20-Oct-07 21-Oct-07
Janet 22-Oct-07
Joan 25-Oct-07

Column C isn't filled in until the person leaves. Therefore the counts
should be:
3 Oct ....1
:
10 Oct ... 4
:
25 Oct .... 4

I have a table showing all the dates in column g and the counts for h. The
array formula I have tried for h is
{sumproduct(--(b1:b10<=g3)*(--(c1:c10>=g3)))+sumproduct(--(b1:b10<=g3)*(--(c1:c10="")))}


What am I doing wrong? Thanks for the help

 

[Guest]

Actually I should modify this a bit....
My table showing the people is not limited in that in 2 hours I might add
other people. So in actuality my formula is

{sumproduct(--(b1:b100<=g3)*(--(c1:c100>=g3)))+sumproduct(--(b1:b100<=g3)*(--(c1:c100="")))}


but it includes a count for all the blank lines. So for 3 Oct I get 90.

 

[Ron Coderre]

Perhaps this ARRAY FORMULA (committed with Ctrl+Shift+Enter):

=SUM(($B$1:$B$100>0)*($B$1:$B$100<=G3)*(IF($C$1:$C$100="",G3,$C$1:$C$100)>=G3))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)