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

Options
✭✭✭✭

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:

• ✭✭✭✭
Options

Thanks, Genevieve!

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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.

• ✭✭✭✭
edited 01/30/23
Options

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?

Options

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

• ✭✭✭✭
Options

Thanks, Genevieve!

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

• ✭✭✭✭
edited 01/31/23
Options

@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?

Options

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

• ✭✭✭✭
Options

Thanks, @Genevieve P. ! That worked perfectly!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!