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 Followup Meeting?]@row = 1, [Quote/Proposal Issued?]@row = 1, [Finalist?]@row = 1, Resolution@row = 1, "1"))))
Any help or guidance is greatly appreciated :)
Best Answer

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

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.

Thanks, Paul!
So, should I add a parenthesis after Resolution@row = 1?
=IFS([1st Meeting Complete?]@row = 1, [SME Followup Meeting?]@row = 1, [Quote/Proposal Issued?]@row = 1), 1)
I'm still getting an UNPARSEABLE error, so I'm definitely doing something wrong.

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 Followup 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

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

@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

Thanks, @Genevieve P. ! That worked perfectly!
Help Article Resources
Categories
Check out the Formula Handbook template!