Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Help Calculating Variable Status

I have a checklist with 7 items. Each item is a yes/no drop down, and they can be completed in any order. I need to automate (via formula) one of 3 statuses based on the following conditions:

1) No response (yes or no) in any of the 7 columns - all blank. Status: Not Started

2) Responses (yes or no) in 1-6 of the columns, any order or combination - 1 or more blanks. Status: In Progress

3) Responses in all columns (yes or no) - no blanks. Status: Complete

Is there a function or formula that can help me assign the status based on those criteria?


Thank you

Best Answer

  • ✭✭✭✭✭✭
    Answer ✓

    Hey @AFlint

    I will presume that your 7 columns are contiguously placed on your sheet - in other words, all side by side together.

    =IF(COUNT([Column1]@row:[Coulumn7]@row)=0, "Not Started", IF(COUNT([Column1]@row:[Coulumn7]@row)=7, "Complete", IF(AND(COUNT([Column1]@row:[Column6]@row)>0, COUNT([Column1]@row:[Column6]@row)<6), "In Progress")))

    For the In Progress - what is the state of Column7? Are we missing an additional AND criteria?

    Does this formula work for you?

    Kelly

Answers

  • ✭✭✭✭✭✭
    Answer ✓

    Hey @AFlint

    I will presume that your 7 columns are contiguously placed on your sheet - in other words, all side by side together.

    =IF(COUNT([Column1]@row:[Coulumn7]@row)=0, "Not Started", IF(COUNT([Column1]@row:[Coulumn7]@row)=7, "Complete", IF(AND(COUNT([Column1]@row:[Column6]@row)>0, COUNT([Column1]@row:[Column6]@row)<6), "In Progress")))

    For the In Progress - what is the state of Column7? Are we missing an additional AND criteria?

    Does this formula work for you?

    Kelly

  • ✭✭✭✭

    Hi @Kelly Moore

    1) Yes, the columns are contiguous in sequence

    2) I actually worked out something similar to your post

    =IF(COUNTIF([First Column]@row:[Last Column]@row, ="") = 7, "Not Started", IF(COUNTIF([First Column]@row:[Last Column]@row, ="") > 0, "In Progress", "Review Complete"))

    As you can see, I focused on the blanks and used the 7 to indicate all blanks, then if anything ">0" is not blank, that means it is started, otherwise it is complete.

    Thank you for the help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions