Oct 05 2021 01:48 PM
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!!
Event Sponsor;Friend of Staff |
Event Sponsor;Staff - Former |
Event Sponsor;Sponsor Letter;Vendor |
Staff |
Staff;Event Sponsor |
Event Sponsor;Staff |
Oct 05 2021 02:09 PM
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.
Oct 05 2021 02:13 PM - edited Oct 05 2021 02:14 PM
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","")
Oct 05 2021 02:14 PM
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!!
Oct 05 2021 04:17 PM
SolutionTo replace FALSE on empty string
=IF( COUNTIF(F2,"*"&"Staff - Former"&"*" ), "",
IF( COUNTIF(F2,"*"&"Friend of Staff"&"*"), "",
IF( COUNTIF(F2,"*"&"Staff"&"*"),"Staff",
"" )))
Oct 05 2021 04:17 PM
SolutionTo replace FALSE on empty string
=IF( COUNTIF(F2,"*"&"Staff - Former"&"*" ), "",
IF( COUNTIF(F2,"*"&"Friend of Staff"&"*"), "",
IF( COUNTIF(F2,"*"&"Staff"&"*"),"Staff",
"" )))