In partnership with

Multiple Criteria Checkboxes

I love it when y’all ask me spreadsheet questions; it gives me unique challenges to work out. I had a viewer reach out to me on LinkedIn to ask about today’s topic after watching one of my previous videos on YouTube.

Here’s what we’re doing:

1. Organizing tabled checklists

2. Organizing tabled dropdown lists

3. Linking the two with COUNTIF and ARRAYFORMULA

Here’s the full walkthrough over on YouTube:

Tables

We’ve got tables in Google Sheets now, so I expect I’ll use them for…EVERYTHING from now on. 😀

Let's convert both our data ranges to tables so we can more easily access them in our formulas in the final steps of this walkthrough.

In our first data set, we’ve got a list of lessons (1-9) and columns for each student. Below each student’s name, is a checkbox indicating whether they have completed that row’s lesson.

This is the part we want automated from our other sheet.

Let’s create a named range, student_names, for the names of the students in the header row of our table. Because it’s a row and not a column, we have to create this range manually. As we’ll see in a moment, the Lessons will be able to be used using table reference format: Students[Lesson].

Dropdown Lists

Now we have a lesson plan data range. It will include dates, student names and the lesson to be assigned.

We create dropdown lists for the students and the lesson plans from the list of students and lessons in our first table.

To do this, we apply data validation to our Student column in our table. We select Dropdown (from a range). Then we enter =student_names as the range. This will pull in that row of names for each student from our Students table.

Our second dropdown list is similar, only this time we can use the table column reference instead of an actual named range. This is very useful since if we add a new lesson to the table, it will automatically be included in our dropdown list here.

Link and Automate

We want the sheet to do the work for us. When we enter a new lesson in the Lessons table, we want the Students table to automatically check off that lesson under the corresponding student’s name.

There are probably other ways to do this (let me know if you’ve got a better way by replying to this email), but I opted to create a custom formula that does the following:

1. Concatenates the student name and lesson name from each combination on the Lesson table.

2. Compares whether that combination is the same as each cell reference in the Students table.

3. If it is, set the checkbox to TRUE.

4. If it’s not, leave checkbox as FALSE.

This sounds more complicated than it is.

This formula generates a list of all the name + lesson combinations in our Lesson table: ARRAYFORMULA(Lessons[Student]&Lessons[lesson plan]).

This is what you get if you just enter that. It’s a list of each name plus the lesson.

Now we can do the same sort of concatenation for each cell in our checkbox list. The formula G$3&$E4, for instance, prints DavidLesson1.

Next, we plop a COUNTIF statement in there. This will return 1 if the concatenation from our cell is in our main ARRAYFORMULA list.

By adding a simple IF statement, we can return TRUE or FALSE depending on whether this value is in our ARRAYFORMULA list of names + lessons. We’re saying, “if the count is greater than zero”, return TRUE.

And, voila!

We’ve got a dynamic multiple criteria checkbox tool.

Check out the video walkthrough if it’s still unclear!

Send me more

Big thanks Karen for asking about this functionality. Karen’s an instructor based out of South Africa and I hope this helps out her lesson planning!