% Complete Based on # of Tasks Assigned

ktcran

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)

KPH

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 multi-select 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:

KPH

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 multi-select 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: