Counting dates in a RANGE (yargh!) :)

......A...............................I.........................J
1.......STATUS..........HIRE DATE.........TERM DATE
2.......Active..............1/24/1984...........12/12/2003
~~~... .............. ........... . ...... .............
5403..Terminated.....3/14/2005............5/24/2006

So I have this large list of employee data. I want to find out the base
number of employees who were here in 2004. What I want to do is COUNT
all "Active" employees in the list, then I want to COUNT all those who
were hired AFTER (2004,12,31), and finally COUNT all those who left
[or. "term dated"] DURING 2004. Once I have those three numbers, I
simply take all Active employees, subtract those hired after 2004 and
then add back those who quit during 2004 to give me the base number on
January 1st, 2004.

Formulas I've used are as follows (along with their results) --->

**Counting all Actives
=COUNTIF(A1:A5403,"Active")
Nice and easy, and equals 747.

**Counting all those hired after 2004
=COUNTIF(I1:I5403,">"&DATE(2004,12,31))

A bit more complicated, but works quite nicely. I originally had HUGE
problems due to a sorted list and the dates being input as '12/28/2000
etc... I think the ' was buggering everything. Data --> Text to Columns
fixed it.

**Counting all those who quit DURING 2004 [ie. Jan1st'04<= and
<Dec31st'04
.... this is where I'm stumped. I know that COUNTIF does not accept
multiple arguments but dont know what to use in place of it.

If anyone has some advice, please lend it!

PS - this NG is great, never seen such helpful knowledgeable people

Step three (counting dates between ranges) works by using:

=SUMPRODUCT(--(FM!J$3:J$5403>$E$1),--(FM!J$3:J$5403<=$G$1))

.... where E1 and G1 are the date "limits", respectively

Quite happy to have this finished! Hopefully this can help someone in
the future.

Here's an alternative:

You only need to test for the year:

=SUMPRODUCT(--(YEAR(FM!J$3:J$5403)=2004))

Biff

"S Davis" <theseandavis@gmail.com> wrote in message
news:1146100003.317700.35340@u72g2000cwu.googlegroups.com...
> Step three (counting dates between ranges) works by using:
>
> =SUMPRODUCT(--(FM!J$3:J$5403>$E$1),--(FM!J$3:J$5403<=$G$1))
>
> ... where E1 and G1 are the date "limits", respectively
>
> Quite happy to have this finished! Hopefully this can help someone in
> the future.
>