Conditional Array Functions

[JR]

I've read many posts regarding this subject but so far none have been
close to what I need. I have seen array formulas with nested if
statements that check multiple fields. I'm trying to recreate this
scenario but have been unsuccessful. What I am trying to evaluate is
Microsoft Project data that I have copied and formatted in Excel. I
want to create on a separate spreadsheet a running count of tasks that
need to be updated as of a particular status date (stored on Sheet1,
cell O1).

Here's the layout of the spreadsheet for Rows 4-5000:
Column L - Manager's Names
Column N - Start Dates
Column O - Finish Dates
Column U - Percent Complete

I want the task to be counted if one or more of the following
conditions are met:
1) Manager Name = MgrA

AND

2a) Start Date is earlier than (or equal to) Status Date
AND Percent Complete = 0
OR
2b) Finish Date is earlier than (or equal to) Status Date
AND Percent Complete < 100
OR
2c) Start Date is Percent Complete is between 0 and 100 (exclusive)
AND Start Date is earlier than Status Date
AND Finish Date is later than Status Date

Thanks!

 

[Jerry W. Lewis]

Combine conditions using * as an AND and + as an OR.

I don't understand "Start Date is Percent Complete is ..." in condition
2c. The following presumes that "Start Date is" is a mistake and that
2c should begin "Percent Complete is ..."

=SUMPRODUCT(SIGN((L4:L4000)*((N4:N4000<=O1)*(U4:U4000=0)
+(O4:O4000<=O1)*(U4:U4000<100)
+(N4:N4000<O1)*(O4:O4000>O1)*(U4:U4000>0)*(U4:U4000<1000))))

Jerry