Conditional Text Formula or Equation

Ask the Community Get Answers
1

I’m trying to figure out how to count the values of several fields in my table.

Here’s the setup:

  • I have a table for each “class” our school teaches.

  • Each table has fields for every lesson question, with a multi-choice outcome for each lesson (e.g. “Complete,” “Needs resubmission,” etc).

  • Example: Student Suzy is working on Intro to Archeology. She’s marked “Complete” on 5 of the 7 lessons in that class. I want a way to count those so I can see how many lessons she’s completed.

What I’ve tried:

  • The fields are multi-choice, and I’d like to count how many are set to “Complete.”

  • I thought about adding new number fields for each lesson outcome with conditional rules (if Lesson 1 = Complete, then 1), but this isn’t practical since I have 64 courses and each has multiple lessons.

My question:
Is there a way (via a formula or another approach) to count the values across fields X, Y, Z only if they are “Complete,” and return the total number?

2

Hi @Leah,
In your case, it’s not super simple to get a count without, as you suggested, adding new number fields.

What I’d suggest instead is restructuring/normalising your database to merge all the classes into one table, and all enrollments of that class into another.

image
image769×351 6.84 KB

Pros:

  • Add a count of completed and total lessons in the Enrollments table.
  • Add more data about lessons that a student has completed (e.g. status, date, etc)

Cons:

  • More record consumption
  • You’d need some sort of automation to copy all of the Enrollment Lesson records when a new Enrollment is added.

I’d be happy to consult with you on this if you’d like to reach out to me directly.

I hope that helps in some way!