How can I apply conditional formatting to a Google Sheet to show three checkboxes minimum are checked?

Question

I work in a school where I used a Google Sheet to track student attendance.

If a student is late or absent, I check a checkbox.

I would like to find a formula to automatically turn a line red if three checkboxes in the line have been checked so I know which students need to get a warning.

Can anybody help?

Answer 1

Here's how to implement it:

1. Select your entire range that contains checkboxes.

2. Choose Format > Conditional formatting from the menu.

3. In the Conditional Formatting dialog box, under "Format cells if...", use the drop-down menu to select "Custom formula is" (bottom of the drop-down list).

4. In the text box below that, enter this formula:

=COUNTIF(INDIRECT(ROW()&":"&ROW()),"="&TRUE)>=3

5. In the "Formatting style" section, click the paint can icon and choose red for the background (and perhaps the "B" for bold type as well).

6. Click the blue "Done" button and close the dialog box.

Answer 2

Attempt at answering 'why does this work' question.

This formula assumes the cells linked to these three checkboxes are on the same row for each student. A checkbox writes '1' into a cell if it's checked, and '0' if it's not checked. Therefore, the total for all 3 checkboxes is at least three if all three are checked.

The "row()" part of the formula makes it more flexible since we don't know which rows you're using. The formula row() returns the current row number and row() & ":" & row() creates a string that selects the entire row (such as 4:4 means all cells in row 4).

The countif() part generates a result only if a given condition is met (that's the IF part).

What I like to do with these nested formulas is break each part down and write it out on its own line so you can see what is going on.

You could also do a simple SUM() and check if it = 3, but that depends on how the checkbox output cells are used on the sheet.

Answer 3

=COUNTIF(INDIRECT(ROW()&":"&ROW()),"="&TRUE)>=3