overall status based on drop down list

Hello,

would you please guide me what function shall i use in excel, where:

- cell A2 is overall status
- cells A3:A7 has drop down list with "not started", "passed", "failed"

for example:

A2 = FAILED
A3 - passed
A4 - failed
A5 - passed

A2 = PASED
A3 - passed
A4 - passed
A5 - passed

A2 = In progress
A3 - passed
A4 - not started
A5 - passed

Results in overall status in cell A2 i am looking for is:

not started = A3:A7 contains only "not started"
passed = A3:A7 contains only "passed"
Failed = A3:A7 contains one cell with "Failed"
in process = A3:A7 contains in one cell cell passed and the rest not started

many thanks for any advice.
Helena

I advice you to use pivot table for that kind of work.


HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
Dismiss

Try this formula in A2:

Formula:

Please Login or Register  to view this content.

Just so you are aware there are some criteria that could happen where a consequence is not stated. An example would be what would happen if two cells are "Passed" and the rest of the cells are "Not Started". You have a condition for if one cell is passed and the rest are not started. Just wanted to alert you too this.

formula works for me. It would help if you included a sample sheet.

The problem is that in the original post you made you based your criteria on "Passed, Failed". In the sample workbook your criteria is actually "Pass" or Fail". This is why we ask for a sample worksheet so we can specify formulas to what you truly need.
See below formula and put in A2 and drag across.

Formula:

Please Login or Register  to view this content.

If the above formula does not work it might be due to needing ; instead of , based on your locale. Try below formula where commas are replaced with semicolons:

Formula:

Please Login or Register  to view this content.