SOLVED

if countif help

New Contributor

Here's my need. Is it possible?

 

I have a column F with group identifiers. I want the formula to say "Staff" if the cell in column F says staff. It is doing that perfectly! But wait!! I also have former staff and friend of staff - I only want it to say staff if it says ONLY staff (not former or friend). Here is the syntax of the formula - I just don't know how to do it properly:

 

Current formula: =IF(COUNTIF(F2,"*"&"Staff"&"*"),"Staff","")

What I want: =IF((COUNTIF(F2,"*"&"Staff - Former"&"*"),"",IF((COUNTIF(F2,"*"&"Friend of Staff"&"*"),"",IF((COUNTIF(F2,"*"&"Staff"&"*"),"Staff")))

 

But I'm getting this error. Help!! Thank you!!

askjacq_0-1633466845328.png

 

 

Event Sponsor;Friend of Staff
Event Sponsor;Staff - Former
Event Sponsor;Sponsor Letter;Vendor
Staff
Staff;Event Sponsor
Event Sponsor;Staff
6 Replies

@askjacq 

Didn't check the logic behind, but this formula

=IF( COUNTIF(F2,"*"&"Staff - Former"&"*" ), "",
 IF( COUNTIF(F2,"*"&"Friend of Staff"&"*"), "",
 IF( COUNTIF(F2,"*"&"Staff"&"*"),"Staff"   )))

have no errors.

@askjacq 

Why do you use COUNTIF here? You can simply use

 

=IF(F2="Staff","Staff","")

 

or if you want to return "Staff" for "Event Sponsor;Staff" too:

 

=IF(ISNUMBER(FIND(";Staff;",";"&F2&";")),"Staff","")

I copied your formula and excel let me tab out of it, so we are so much closer. But I'm getting "false" instead of an empty cell. How do I fix that?

=IF( COUNTIF(F2,"*"&"Staff - Former"&"*" ), "", IF( COUNTIF(F2,"*"&"Friend of Staff"&"*"), "", IF( COUNTIF(F2,"*"&"Staff"&"*"),"Staff" )))

 

askjacq_0-1633468446999.png

Thank you!!

 

@Sergei Baklan 

best response confirmed by askjacq (New Contributor)
Solution

@askjacq 

To replace FALSE on empty string

=IF( COUNTIF(F2,"*"&"Staff - Former"&"*" ), "",
 IF( COUNTIF(F2,"*"&"Friend of Staff"&"*"), "",
 IF( COUNTIF(F2,"*"&"Staff"&"*"),"Staff",
     ""   )))

 

That worked! Thank you, Sergei!!

@askjacq , you are welcome, glad to help