Formula -- If next 4 Columns say Yes/No say something

Nick Burrus
Nick Burrus ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions



I have a sheet with six columns. They are:

Name | Current Step | Step 1 | Step 2 | Step 3 | Step 4


Step 1 - 4 have Yes/No written in them for completion status. 


Can I have a formula in Current Step to count how many "Yes" there are and print the Step?


Can someone direct me to where I can find out how to code this?


I was thinking of like =IF(COUNTIF(RIGHT, "Yes")=0, "Empty", "Test") or something like that. It is column based, but, how do I count all columns to the right? Or will I need to add them in manually?


But I am not 100% sure how to get it to be the values. I'm thinking like:

  • If 0 Yes, say "None"
  • If 1 Yes, say "Step 1"
  • If 2 Yes, say "Step 2"
  • If 3 Yes, say "Step 3"
  • If 4 Yes, say "Step 4"

Edit; I'm closer =COUNTIF([Step 1]24:[Step 4]24, "Yes") is returning the number of "Yes" in my columns, ie this one would say 2. 


Now how do I make the 2 say "Step 2"?


Edit 2: Maybe if I can do a =IF([Current Step]24 = 1, "QA", "No") I can make the numbercal column  hidden?

Dr. St Nicholas Burrus DHA, PMP

I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!