We have a worksheet that tracks approx 400 applications and each application
can have up to 30 project plans.

We track the status of each plan (ie completed, n/a, open, or a blank value
means a plan is not expected). There are 30 plan status columns per
application starting in column C.
For example, the 1st application may have Plan #1 Status as "open", Plan #2
status as "N/a", Plan #3 Status is blank, Plan #4 status as "completed", etc
etc etc up to approx 30 status columns.

I would like to a quick way to automate an overall summary status for each
application. If any of the status's are "open", then the overall status
should be "open". If all are "completed" or "N/a" or blank, then the overall
status should be "completed".

Without doing a huge IF/AND/OR statement, is there an easy way to sum this
up? By arrays perhaps?

You can use an expression like the following:

=IF(ISNUMBER(FIND(A1:A30,"Open")),"Open",IF(<repea t for other statuses)

The FIND function will search the range for a text string matching the
second argument, and return the position of that match if found. ISNUMBER
essentially converts the outcome to TRUE or FALSE, which is used by the IF
statement to trigger one of two outcomes. Build out the expression to cover
the other statuses, and you should be all set. My only concern is if the
status cells are not in a single range you can pass to the FIND function.

HTH. Write back if stuck.

"MLK" wrote:

We have a worksheet that tracks approx 400 applications and each application
can have up to 30 project plans.

We track the status of each plan (ie completed, n/a, open, or a blank value
means a plan is not expected). There are 30 plan status columns per
application starting in column C.
For example, the 1st application may have Plan #1 Status as "open", Plan #2
status as "N/a", Plan #3 Status is blank, Plan #4 status as "completed", etc
etc etc up to approx 30 status columns.

I would like to a quick way to automate an overall summary status for each
application. If any of the status's are "open", then the overall status
should be "open". If all are "completed" or "N/a" or blank, then the overall
status should be "completed".

Without doing a huge IF/AND/OR statement, is there an easy way to sum this
up? By arrays perhaps?

Hi, I tried yours out and it works, but that gave me an idea to simplify it
more by using countif statments and checking the total of opens.

Thanks very much for your prompt reply and assistance.



"andy62" wrote:

You can use an expression like the following:

=IF(ISNUMBER(FIND(A1:A30,"Open")),"Open",IF(<repea t for other statuses)

The FIND function will search the range for a text string matching the
second argument, and return the position of that match if found. ISNUMBER
essentially converts the outcome to TRUE or FALSE, which is used by the IF
statement to trigger one of two outcomes. Build out the expression to cover
the other statuses, and you should be all set. My only concern is if the
status cells are not in a single range you can pass to the FIND function.

HTH. Write back if stuck.

"MLK" wrote:

We have a worksheet that tracks approx 400 applications and each application
can have up to 30 project plans.

We track the status of each plan (ie completed, n/a, open, or a blank value
means a plan is not expected). There are 30 plan status columns per
application starting in column C.
For example, the 1st application may have Plan #1 Status as "open", Plan #2
status as "N/a", Plan #3 Status is blank, Plan #4 status as "completed", etc
etc etc up to approx 30 status columns.

I would like to a quick way to automate an overall summary status for each
application. If any of the status's are "open", then the overall status
should be "open". If all are "completed" or "N/a" or blank, then the overall
status should be "completed".

Without doing a huge IF/AND/OR statement, is there an easy way to sum this
up? By arrays perhaps?