Excel Nested Logic Formula

Question

Good Afternoon All,

I have a sheet where I am trying to evaluate the status of all items in column b if the corresponding value in column indicates that column b is relevant.

Phase Required  Status
Not Applicable  Incomplete
Not Applicable  Incomplete
Not Applicable  Incomplete

In the above table each row contains a drop down menu. The Phase required column contains the options Not Applicable, Soft Launch or Hard launch. The status column contains the options Incomplete or Complete.

I want to have two separate formulas at the bottom of the sheet. One will validate if all soft launch criteria has been met and the other if all hard launch criteria has been met. So far I've tried nested Ifs, I've tried using index match and an if and. Essential the formulas will need to evaluate all entries in A to determine if they are in the correct phase (soft launch, hard launch, or not applicable), if they are looking for soft launch items and the value is anything other, that row gets ignored, then if the row is applicable it would also need to check if that status is complete. If the phase matches and status is complete for all entries the formula would display ready, else not ready would be displayed.

Does anyone have any ideas?

Add entries into your mock data that shows what you are trying to describe and also show your expected output in the data. — Scott Craner

Kyle Kyle · Accepted Answer · 2016-10-13T19:11:34

I will assume your "Phase Required" is Column A and "Status" is Column B. In that case, the check for "Soft Launch" will be:

=IF(COUNTIFS(A1:A100,"Soft Launch",B1:B100,"<>" & "Complete")>0,"Not Ready","Ready")

Change the range to be whichever rows in columns A and B you need, just make sure they are the same rows. The same would work for hard launch by changing "Soft Launch" to "Hard Launch".

Excel Nested Logic Formula

2 Answers