% Complete Based on # of Tasks Assigned
I need a Smartsheet formula that will calculate the percentage complete for a selected cell based on the count of values in the same row in column "Assigned To (Team)" and the count of value "Complete" in the same row.
We have 3 teams that complete separate tasks for one project, which is created as a new row from a form. Column "Assigned To (Team)" and 3 separate status columns (one for each team) are all dropdown lists allowing multiple values per row. Not all projects will require all three teams.
Example: If column "Assigned To (Team)" has 3 values in cell B1 and there are only 2 cells in row B containing (but may not be equal to) value "Complete" then percentage complete should equal 67%.
ChatGPT gave me =IF(COUNTIF([Assigned To (Team)]1, "*") > 0, COUNTIF([Assigned To (Team)]1, "*Complete*") / COUNTIF([Assigned To (Team)]1, "*") * 100, 0) and it just gave me zeroes in SS  the attached example in excel works with =IF(COUNTIF(A2, "*") > 0, COUNTIF(B2:D2, "*Complete*") / COUNTIF(B2:D2, "*") * 100, 0)
Best Answer

Hi @ktcran
The formula ChatGPT gave you is for excel. There is no * wildcard in smartsheet. That formula will be counting stars which you don't have.
I am hoping this is the requirement (I changed the parts in bold)
If column "Assigned To (Team)" has 3 values in cell A2 and there are only 2 cells in row 2 containing (but may not be equal to) value "Complete" then percentage complete should equal 67%.
If you are using dropdown list columns and Assigned To allows multiselect you can use a COUNTM formula to find your denominator.
=COUNTM([Assigned To]@row)
Will count how many elements are in the Assigned To column.
Then for your numerator you can count how many equal "Complete".
=COUNTIF([Team A Status]@row:[Team C Status]@row, "Complete")
And when we put those together we have:
=COUNTIF([Team A Status]@row:[Team C Status]@row, "Complete")/COUNTM([Assigned To]@row)
The result would look like this:
Answers

Hi @ktcran
The formula ChatGPT gave you is for excel. There is no * wildcard in smartsheet. That formula will be counting stars which you don't have.
I am hoping this is the requirement (I changed the parts in bold)
If column "Assigned To (Team)" has 3 values in cell A2 and there are only 2 cells in row 2 containing (but may not be equal to) value "Complete" then percentage complete should equal 67%.
If you are using dropdown list columns and Assigned To allows multiselect you can use a COUNTM formula to find your denominator.
=COUNTM([Assigned To]@row)
Will count how many elements are in the Assigned To column.
Then for your numerator you can count how many equal "Complete".
=COUNTIF([Team A Status]@row:[Team C Status]@row, "Complete")
And when we put those together we have:
=COUNTIF([Team A Status]@row:[Team C Status]@row, "Complete")/COUNTM([Assigned To]@row)
The result would look like this:
Help Article Resources
Categories
Check out the Formula Handbook template!