SOLVED

New Contributor

# if countif help

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!!

6 Replies

# Re: if countif help

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.

# Re: if countif help

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","")

# Re: if countif help

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" )))

Thank you!!

best response confirmed by askjacq (New Contributor)
Solution

# Re: if countif help

To replace FALSE on empty string

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

# Re: if countif help

That worked! Thank you, Sergei!!