• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Output Basis of a word present in a specified range

S

Sriram Iyengar

New Member
Hi All,

I am trying to create a format wherein for every task there are 4 statuses (WIP, Pending, Completed, N/A). The milestone will be marked complete as if the above range does not have any task pending. The formula I used is countif in an adjacent cell
Formula: =COUNTIF(E4:E12,"Pending"). This gave me the output in cell E3 as 0
Then on the Milestone completion cell I used the formula =IF(E3>=1, " NO ", " Yes ").

Is there someway both functions can be used in a single cell alternatively any other formula that I can use?
 
Hi Sriram ,

You can certainly combine both the formulae in one cell as follows :

=IF(COUNTIF(E4:E12,"Pending")>=1 , " NO " , " Yes ")

Narayan
 
Thanks Narayan - Tht did the trick.... I just realized that I did not word my question properly....
If any one of the tasks is set to WIP my overall status should be WIP. ... If any one task is set to Pending - Overall status is pending & if all tasks are set to complete then overall status is complete
 
Hi Sriram ,

Try this :

=IF(OR(E4:E12="WIP"),"WIP",IF(OR(E4:E12="Pending"),"Pending",IF(AND(E4:E12="Completed"),"Completed","N/A")))

This formula is to be entered as an array formula i.e. after typing in ( or copying and pasting ) the formula , you should press the key combination CTRL SHIFT ENTER.

Narayan
 
Hi,

Since you have three outcomes only, so another array solution:

=LOOKUP(MIN(LEN(E4:E12)),{3,7,8},{"WIP","PENDING","COMPLETE"})

Confirm with Ctrl+Shift+Enter.

Regards,
 
Narayan's formula could be used as below with normal entry:
=IF(COUNTIF(E4:E12,"WIP"),"WIP",IF(COUNTIF(E4:E12,"Pending"),"Pending",IF(COUNTIF(E4:E12,"Completed")=COUNTA(E4:E12),"Completed","N/A")))

Somendra,
N/A is also a possibility as per post#1 by OP which may not get handled in your formula.
 
shrivallabha - That did the trick - I only had to make a small change in the formula at the end....
=IF(COUNTIF(E4:E12,"WIP"),"WIP",IF(COUNTIF(E4:E12,"Pending"),"Pending",IF(COUNTIF(E4:E12,"Completed")=COUNTA(E4:E12),"Completed","N/A"))) I changed the last N/A to Completed
 
You must log in or register to reply here.
Back
Top