SOLVED

if countif help

%3CLINGO-SUB%20id%3D%22lingo-sub-2814855%22%20slang%3D%22en-US%22%3Eif%20countif%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2814855%22%20slang%3D%22en-US%22%3E%3CP%3EHere's%20my%20need.%20Is%20it%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20column%20F%20with%20group%20identifiers.%20I%20want%20the%20formula%20to%20say%20%22Staff%22%20if%20the%20cell%20in%20column%20F%20says%20staff.%20It%20is%20doing%20that%20perfectly!%20But%20wait!!%20I%20also%20have%20former%20staff%20and%20friend%20of%20staff%20-%20I%20only%20want%20it%20to%20say%20staff%20if%20it%20says%20ONLY%20staff%20(not%20former%20or%20friend).%20Here%20is%20the%20syntax%20of%20the%20formula%20-%20I%20just%20don't%20know%20how%20to%20do%20it%20properly%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20formula%3A%26nbsp%3B%3DIF(COUNTIF(F2%2C%22*%22%26amp%3B%22Staff%22%26amp%3B%22*%22)%2C%22Staff%22%2C%22%22)%3C%2FP%3E%3CP%3EWhat%20I%20want%3A%20%3DIF((COUNTIF(F2%2C%22*%22%26amp%3B%22Staff%20-%20Former%22%26amp%3B%22*%22)%2C%22%22%2CIF((COUNTIF(F2%2C%22*%22%26amp%3B%22Friend%20of%20Staff%22%26amp%3B%22*%22)%2C%22%22%2CIF((COUNTIF(F2%2C%22*%22%26amp%3B%22Staff%22%26amp%3B%22*%22)%2C%22Staff%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I'm%20getting%20this%20error.%20Help!!%20Thank%20you!!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22askjacq_0-1633466845328.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315400iE932B54589E0F413%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22askjacq_0-1633466845328.png%22%20alt%3D%22askjacq_0-1633466845328.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22484px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22484px%22%3EEvent%20Sponsor%3BFriend%20of%20Staff%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22484px%22%3EEvent%20Sponsor%3BStaff%20-%20Former%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22484px%22%3EEvent%20Sponsor%3BSponsor%20Letter%3BVendor%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EStaff%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EStaff%3BEvent%20Sponsor%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEvent%20Sponsor%3BStaff%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2814855%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2814965%22%20slang%3D%22en-US%22%3ERe%3A%20if%20countif%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2814965%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1020590%22%20target%3D%22_blank%22%3E%40askjacq%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDidn't%20check%20the%20logic%20behind%2C%20but%20this%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%20COUNTIF(F2%2C%22*%22%26amp%3B%22Staff%20-%20Former%22%26amp%3B%22*%22%20)%2C%20%22%22%2C%0A%20IF(%20COUNTIF(F2%2C%22*%22%26amp%3B%22Friend%20of%20Staff%22%26amp%3B%22*%22)%2C%20%22%22%2C%0A%20IF(%20COUNTIF(F2%2C%22*%22%26amp%3B%22Staff%22%26amp%3B%22*%22)%2C%22Staff%22%20%20%20)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ehave%20no%20errors.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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