Google-sheets – 3 Conditions IFS not behaving as expected

google sheets

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?

Best Answer

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)

Related Topic