Forum Discussion
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!!
| Event Sponsor;Friend of Staff |
| Event Sponsor;Staff - Former |
| Event Sponsor;Sponsor Letter;Vendor |
| Staff |
| Staff;Event Sponsor |
| Event Sponsor;Staff |
To replace FALSE on empty string
=IF( COUNTIF(F2,"*"&"Staff - Former"&"*" ), "", IF( COUNTIF(F2,"*"&"Friend of Staff"&"*"), "", IF( COUNTIF(F2,"*"&"Staff"&"*"),"Staff", "" )))
6 Replies
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","")
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.
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!!
To replace FALSE on empty string
=IF( COUNTIF(F2,"*"&"Staff - Former"&"*" ), "", IF( COUNTIF(F2,"*"&"Friend of Staff"&"*"), "", IF( COUNTIF(F2,"*"&"Staff"&"*"),"Staff", "" )))
