Changing Status to a number in order to chart

10/31/19 Edited 12/09/19

Hello! I am needing to make a widget in a dashboard t show the number of "complete, not started, or in progress" items I have. I know I need to change the status to a number. I have tried to do this with "IF logic but am failing. Can someone help with a formula for the following case?

If status is Not Started = 1

If status is In Progress = 2

If status is Completed = 3

Thank you!!!

if case.PNG

Popular Tags:

Comments

  • This only counts cells that are at a certain "Level" in the heirarchy and then it counts the "Health" for the given condition. In this case "red". You'll need a formula for each color. There are a million variations. Check out the courses around advanced formulas.

    =COUNTIFS($Level$9:$Level$242, ="", $Health$9:$Health$242, "red")

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi,

    Not sure I follow!

    Do you want to show a number, or do you want to count how many there are of each value?

    Here's an example of the former.

    Try something like this.

    =IF([email protected] = "Not Started"; "1"; IF([email protected] = "In Progress"; "2"; IF([email protected] = "Completed"; "3")))

    The same version but with the below changes for your and others convenience.

    =IF([email protected] = "Not Started", "1", IF([email protected] = "In Progress", "2", IF([email protected] = "Completed", "3")))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    I hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Just try this...

     

    =COUNTIFS(Status:Status, "Not Started")

    =COUNTIFS(Status:Status, "In Progress")

    =COUNTIFS(Status:Status, "Completed")

    .

    These three formulas will show you "...the number of "complete, not started, or in progress" items..." you have.

  • This is exactly what I needed. THANK YOU SO MUCH!!!!

  • Thank you so much for this help! This did work as well, but I explained it wrong. The comment below is the formula I was looking for. Rookie mistake :( 

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Happy to help!

    No worries! The important thing is that you got a working solution!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.