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€¦€¦

Maybe all of the MVP's are busy now. Will I do.

=SUMPRODUCT((a2:a6=100)*(MONTH(c2:c6)<12))
OR if you want it for the ones assigned in the month
=SUMPRODUCT((a2:a6=100)*(MONTH(b2:b6)=12)*(c2:c6<2 ))

--
Don Guillett
SalesAid Software

"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..

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..

N Harkawat wrote...
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))
....

There's a syntax error, so it wasn't tested. But it's close. If the
date threshold were entered in a cell named ByDate, make the formula

=SUMPRODUCT(--('Master Sheet 1'!A2:A3000=A25),
--('Master Sheet1'!B2:B3000<=ByDate),
('Master Sheet1'!C2:C3000=ByDate)+(TRIM('Master Sheet1'!C2:C3000)=""))

Also, reread the OP's specs and sample intended results. Following your
approach for Dec 2004, if ByDate were 1 Dec 2004, then the activity 100
begun on 25 Dec 2004 wouldn't be included in the Dec 2004 result. It's
pretty clear the OP's ByDate values are the last days rather than the
first days of the given months.

Thank you sooo much. I have been going crazy trying to work this out! I
used a little from each one and tailored it to my spreadsheet. It turned out
great and now it automatically creates a graph so that I can see the lastest
and greatest reports.
Again THANK YOU!!!!!!!

"Harlan Grove" wrote:

N Harkawat wrote...
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))
....

There's a syntax error, so it wasn't tested. But it's close. If the
date threshold were entered in a cell named ByDate, make the formula

=SUMPRODUCT(--('Master Sheet 1'!A2:A3000=A25),
--('Master Sheet1'!B2:B3000<=ByDate),
('Master Sheet1'!C2:C3000=ByDate)+(TRIM('Master Sheet1'!C2:C3000)=""))

Also, reread the OP's specs and sample intended results. Following your
approach for Dec 2004, if ByDate were 1 Dec 2004, then the activity 100
begun on 25 Dec 2004 wouldn't be included in the Dec 2004 result. It's
pretty clear the OP's ByDate values are the last days rather than the
first days of the given months.