I'm building a commission tracker at my work and I need to search a specific cell and produce 1 of 3 values (in a different cell) based on the text string that the cell holds.

Here is the logic:

  • If cell is empty put error message in cell
  • If cell contains "No Metrics" as the string, populate a 0 in the cell
  • If cell contains any strings other than "No Metrics" populate 50 in cell

Here is my formula:

=IFS('Won Opportunities - Sales'!AQ26="", "SALES PERSON IS GARBAGE",'Won Opportunities - Sales'!AQ26="<>No Metrics",50,'Won Opportunities - Sales'!AQ26="No Metrics",0)

This works fine for empty cells and cells that contain "No Metrics" but gives back #N/A for anything else.

What am I missing here?

2 Answers 2


Roy, you've got a syntax error. Try this:

=IFS('Won Opportunities - Sales'!AQ26="", "SALES PERSON IS GARBAGE",'Won Opportunities - Sales'!AQ26<>"No Metrics",50,'Won Opportunities - Sales'!AQ26="No Metrics",0)

  • That worked! Thank you so much! I was thinking its a simple syntax error, but couldn't figure it out.
    – Roy Eldar
    Commented Nov 5, 2018 at 18:27
  • We ALL get "blind" to our own stuff occasionally, Roy. Glad to help shed some light.
    – Erik Tyler
    Commented Nov 6, 2018 at 4:40

Some alternatives that tell the story as well:

=IF(A1="","SALES PERSON IS GARBAGE",IF(A1="No Metrics",0,50))


=SWITCH(A1,"","SALES PERSON IS GARBAGE","No Metrics",0,50)
  • Great to know. Thank you!
    – Roy Eldar
    Commented Nov 7, 2018 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.