This works for me to list all jobs that were open before Feb-1-2005 and are
either completed in Feb -2005 or are still in progress

=SUMPRODUCT(--('Master Sheet 1'!A2:A3000=A25),--('Master
Sheet1'!B2:B3000<=datevalue("02/01/2005")),--('Master
Sheet1'!C2:C3000=datevalue("02/01/2005")+isblank('Master Sheet1'!C2:C3000))

"tkacoo" wrote in message
...
I have a spreadsheet that is linked to a summary sheet.
Activity Codes (Col A) Assigned (Col B) Completed (Col C)
101 12/01/04 1/31/05
100 01/30/05 1/31/05
100 12/25/04 1/15/05
102 02/01/05
101 11/15/04

I need to capture How many of each activity codes are open for a given
month. for example

Dec 2004
Activity Codes Open
100 1
101 2
102 0

Feb 2005
Activity Codes Open
100 0
101 1
102 1

Here is the formula I am using:

=SUMPRODUCT(('Master Sheet 1'!A2:A3000=A25)* ('Master Sheet
1'!B2:B3000<=02/01/2005)* ('Master Sheet 1'!C2:C3000="")*OR ('Master Sheet
1'!C2:C3000<=02/01/2005)

1. I am selecting the specific Activity code I want
2. Then look in the Assigned column and look for any date that is equal to
or less than Feb 1, 2005
3. Then look in the Completed column and return any thing that is Blank or
equal to or less than Feb 1, 2005.
I am coming up with a 0 answer. Any suggestions..