Progress Bar and/or Completion % by Check Box Column Status

Hello! I'm looking to use a formula that will calculate the percentage of tasks completed by row based on whether or not the check box is "checked." I have five check box columns, so if one is checked, the % Completed column shows 20%, if four are checked, 80%, etc. I know I need to use IF/And statements, but I keep getting errors.

Here's what I have done thus far to capture 100% for all columns when checked:

=IF(AND([1st Meeting Complete?]@row = 1, [SME Follow-up Meeting?]@row = 1, [Quote/Proposal Issued?]@row = 1, [Finalist?]@row = 1, Resolution@row = 1, "1"))))

Any help or guidance is greatly appreciated :)

Tags:

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You need to close out the AND after the last criteria - Resolution@row = 1 - and you would only have a single closing parenthesis on the end of that formula since you only have 1 IF statement.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Tony Fronza
    Tony Fronza ✭✭✭✭

    Thanks, Paul!

    So, should I add a parenthesis after Resolution@row = 1?

    =IFS([1st Meeting Complete?]@row = 1, [SME Follow-up Meeting?]@row = 1, [Quote/Proposal Issued?]@row = 1), 1)

    I'm still getting an UNPARSEABLE error, so I'm definitely doing something wrong.

  • Tony Fronza
    Tony Fronza ✭✭✭✭
    edited 01/30/23

    Disregard! I just remembered that I need to correct another error. It is working now.

    So, should I continue the IF/AND logic for the other scenarios as follows:

    =IFS([1st Meeting Complete?]@row = 1, [SME Follow-up Meeting?]@row = 0, [Quote/Proposal Issued?]@row = 0), .33)

    Also, one of the five columns is a Yes/No dropdown - how do I include "If (Column@row) is NOT BLANK?

  • Hi @Tony Fronza

    To clarify, you'll always want to use IF(AND(...) versus IFS .

    Here's how the structure would go with multiple statements:

    =IF(AND(---), 1, IF(AND(---), 0.8, IF(AND(---), 0.6, IF(AND(---), 0.4, IF(AND(---), 0.2, 0)))))


    My personal favourite way to say "not blank" is to use <> for does not equal and "" for blank, like so:

    [Column Name]@row <> ""


    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Tony Fronza
    Tony Fronza ✭✭✭✭
    Answer ✓

    Thanks, Genevieve!

    I don't know why I used IFS above (I originally used AND). Thanks for clarifying, though!

  • Tony Fronza
    Tony Fronza ✭✭✭✭
    edited 01/31/23

    @Genevieve P. Sorry, one last question. I'm trying to get the Completion % to populate as 100%, but it keeps showing up as "1" instead. I have the column formatted as % - do I need to modify the formula to accommodate?


  • Hi @Tony Fronza

    Make sure that the output of your formula is a number, as in it's not in quotes.

    For example:

    =IF(AND(---), 1, 

    but not

    =IF(AND(---), "1",

    Putting quotes around a number means that the cell will read it as text and will be unable to add a numerical percent format to it.

    Let me know if that helps!

    Cheers,

    Genevieve  

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Tony Fronza
    Tony Fronza ✭✭✭✭

    Thanks, @Genevieve P. ! That worked perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!