Excel IF Formula varififying one cell has data and another cell has specific value

2
votes

I have an excel spreadsheet that contains data for projects that we are working on. I need to output "Assign Me" in cell C3 if A3 contains any data and B3 does not contain 5 different team members names. See image below: I need the Status to read Assign Me ONLY if A1 = also contains data. In row 4 below A4 doesn't contain data... I need the formula to check for the specific names AS WELL as for text in column A. And if Column A doesn't have text it should output blank.

Column C3 formula: =IF(B3="Jon","",IF(B4="Sally","",IF(B3="Connie","",IF(B3="Edgar","",IF(B3="Tony","","Assign Me")))))

This formula doesn't validate there being text in Column A. 

       A         B         C    
1     3/21      Jon             
2     3/21      Oscar   Assigned
3               Oscar   Assigned

Here is what I need.

I need the following expressed in an excel formula If A3 is blank AND IF B3 does not contain Jon, Sally, Connie, Edgar, or Tony then display "Assign Me" in C3. If not, leave C3 blank.

What do I need to add to make it work as intended?

2
excelif-statementexcel-formula
You mentioned you need the result in D1 initially and in your last paragraph, you say it should be in C3. Could you clarify or fix any typo? – Jerry
yeah... sorry I was initially going to add a screenshot... showing my prime example, but I didn't have the "reputation" to give the screenshot and then hurried and set it up with a pseudo one. – mike
That's okay. You still can put the link to a picture in your question if you put it on an image hosting site (until you can directly put pictures), so if there's a next time... :) – Jerry

2 Answers

2
votes

If A3 is blank AND IF B3 does not contain Jon, Sally, Connie, Edgar, or Tony then display "Assign Me" in C3. If not, leave C3 blank.

Try this one in C3:

=IF(AND(A3="",COUNTIF(B3,{"Jon";"Sally";"Connie";"Edgar";"Tony"})=0),"Assign Me","")
0
votes
=IF(AND(A3="",COUNTIF(B3,{"Jon";"Sally";"Connie";"Edgar";"Tony"})=0),"","Assign Me")

Using Simoco's solution worked except that you need to flip the "Assign Me" and blank entry at the end.

I needed to place assign me in column C if the user's name WASN'T in Column B. The solution provided by Simoco checks for the name and assigns it if the name is there. I needed the opposite result.