software

......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 quot;Activequot; 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. quot;term datedquot;] 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) ---gt;

**Counting all Actives
=COUNTIF(A1:A5403,quot;Activequot;)
Nice and easy, and equals 747.

**Counting all those hired after 2004
=COUNTIF(I1:I5403,quot;gt;quot;amp;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 --gt; Text to Columns
fixed it.

**Counting all those who quit DURING 2004 [ie. Jan1st'04lt;= and
lt;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 peopleStep three (counting dates between ranges) works by using:

=SUMPRODUCT(--(FM!J$3:J$5403gt;$E$1),--(FM!J$3:J$5403lt;=$G$1))

.... where E1 and G1 are the date quot;limitsquot;, 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

quot;S Davisquot; gt; wrote in message ups.com...
gt; Step three (counting dates between ranges) works by using:
gt;
gt; =SUMPRODUCT(--(FM!J$3:J$5403gt;$E$1),--(FM!J$3:J$5403lt;=$G$1))
gt;
gt; ... where E1 and G1 are the date quot;limitsquot;, respectively
gt;
gt; Quite happy to have this finished! Hopefully this can help someone in
gt; the future.
gt;