T
Tracey
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
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