IF Function for three conditions? How? PLEASE.

[jpbonono]

Badly needed your help here masters!

You will notice the "remarks" field (cell E, H, K, N, Q, T, W, Z) with a total count of 8. I have three conditions for the "final remark" (cell AC) and these are:

1. if FAILED remark counted less than equal to "7" (seven) but not "0" (zero), FINAL REMARK should be "IRREGULAR".
2. if FAILED remark counted "0" (zero), FINAL REMARK should be "RETAINED".
3. if PROMOTED remark counted "8" (eight) or no FAILED remark is acquired, FINAL REMARK should be "REGULAR"

Here's the screen shot.

Your ASAP help will be very much appreciated.

Thanks guys!

 

[jpbonono]

up

 

[malcmail]

DO you have the number of times FAILED or PROMOTED appear in a line in another cell?

 

[Asala42]

One way:

=IF(COUNTIF($E3:$Z3,"Promoted")=8,"Regular",IF(COUNTIF($E3:$Z3,"Failed")=0,"Retained","Irregular"))

[edit] - it assumes the contents between comments will never contain the words "Promoted" or "Failed".

 

[malcmail]

Spooky - just tried posting very similar and it failed. When I refreshed there was your post.

 

[samthomasny]

1. if FAILED remark counted less than equal to "7" (seven) but not "0" (zero), FINAL REMARK should be "IRREGULAR".
2. if FAILED remark counted "0" (zero), FINAL REMARK should be "RETAINED".
3. if PROMOTED remark counted "8" (eight) or no FAILED remark is acquired, FINAL REMARK should be "REGULAR"

Your #2 condition - "if FAILED remark counted Zero" and the second part of #3 condition "no FAILED remark is acquired" is the same. That's wrong !!

 

[shyy]

I agree with that, its a bit confusing, what do you mean by the first criteria?

These seems to work with a countif approach.

IF failed shows up <=7 to put in "irregular" ? but not "0" (zero) confuses me

1. if FAILED remark counted less than equal to "7" (seven) but not "0" (zero), FINAL REMARK should be "IRREGULAR".

should post a sample data with all the end results.

 

[jpbonono]

I agree with that, its a bit confusing, what do you mean by the first criteria?

These seems to work with a countif approach.

IF failed shows up <=7 to put in "irregular" ? but not "0" (zero) confuses me

1. if FAILED remark counted less than equal to "7" (seven) but not "0" (zero), FINAL REMARK should be "IRREGULAR".

should post a sample data with all the end results.

Your #2 condition - "if FAILED remark counted Zero" and the second part of #3 condition "no FAILED remark is acquired" is the same. That's wrong !!

hi guys, sorry about the confusion. My bad. If FAILED remark counted zero it should be "REGULAR" not "RETAINED"


Let me just rephrase the criteria.

1.) If FAILED remark counted 7 - 1, the FINAL REMARK should say "IRREGULAR".
2.) If FAILED remark counted none (0), it should say "REGULAR". (which means the remark says 8 "PROMOTED")
3.) If FAILED remark counted 8, it should have a FINAL REMARK "IRREGULAR".

Hope this clarifies. Thanks!

 

[jpbonono]

One way:

=IF(COUNTIF($E3:$Z3,"Promoted")=8,"Regular",IF(COUNTIF($E3:$Z3,"Failed")=0,"Retained","Irregular"))

[edit] - it assumes the contents between comments will never contain the words "Promoted" or "Failed".

Hi to this asala! however, i have three remarks, retained, irregular, and regular. Though this really works fine!

 

[jpbonono]

Thanks to everyone! im good with Asala42's formula. ^_^