Add extra COUNITF to formula

[sharky12345]

I've got this formula that I need to expand if someone can help;

=IF(C2="","",(IF(AND(C2<>"",F2<>"Available"),"",IF(COUNTIF(Duties!$B$2:$B$20,C2)=1,"YES","NO"))))

I need it to check O2:O42 and if C2 is found I need it to show "Abstracted" - this should override the "YES" or "NO" part if possible.

 

[etaf]

=IF(C2="","",(IF(AND(C2<>"",F2<>"Available"),"",IF(COUNTIF(Duties!$B$2:$B$20,C2)=1,"YES","NO"))))
you can change to =IF(OR(C2="",AND(C2<>"",F2<>"available")),"",IF(COUNTIF(O2:O42,C2)=1,"abstracted",IF(COUNTIF(Duties!$B$2:$B$20,C2)=1,"YES","NO")))

But if C2 exists More than once in column B or O then it will fail - so can C2 appear more than once ?
just use >0 instead of =1

 

[sharky12345]

Hi, and thanks....

C2 can't appear more than once so your suggestion is good thanks!

 

[etaf]

you are welcome