Formula or Rollup

[Christina_Sturr]

Hi there,

I am trying to set up an OKR table in Airtable.  I have two tables in my workspace.  One for Key Results and one for Initiatives.  Under initiatives I have a column with tasks just check off as they occur.  Please see below.  How do I create a column with a formula that will calculate the % completed based on the check marks listed in the task column.  I have tried to create the formula with creating additional columns (total task count and total task count completed to calculate off of but that didn't work).  I used the "countall tasks" in the total task column and tried to create a formula in the total task count but it isn't working.  

Any guidance would be greatly appreciated.

[Joseph_Roza]

Have you considered making each task an individual record instead of including tasks in a long text field?

Especially with the new List view, it should work quite well, and then you can have another table link to the tasks (records), roll them up, and run calculations.

I've built many task managers, and can't say I've ever used long text fields to track tasks. Just checkbox fields and individual records/nesting records.

[TheTimeSavingCo · Accepted solution]

+1 for what Joseph suggested

If operational requirements don't allow for it and you really need to use a long text field, try creating a formula field with this:

(
	LEN(
		{Tasks}
	) - 
	LEN(
		SUBSTITUTE(
			{Tasks},
			"[x]",
			"[]"
		)
	) 
)/
(
	LEN(
	  {Tasks}
	) - 
	LEN(
	  SUBSTITUTE(
	    {Tasks},
	    "[ ]",
	    "[]"
	  )
	)
	+
	LEN(
	  {Tasks}
	) - 
	LEN(
	  SUBSTITUTE(
	    {Tasks},
	    "[x]",
	    "[]"
	  )
	)
)

[Christina_Sturr]

OMG, that worked.  Thank you so much!  Thank you also Joseph.  I will keep that in mind and may even try this for this project when I have more time.  However, this worked for me to present to my team as an immediate resolution.  Thanks everyone.  This community is very helpful:)