1

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?

  • Tallinn can you create a demo sheet with your desired output? (enable edit access) there are a number of ways to do this – Manakin Oct 26 '18 at 6:36
  • Datanovice, I don't know how to do that. I was hoping to find a formula I can use with conditional formatting; any ideas for that ? – TallinParis Oct 26 '18 at 7:26
  • Yep, you could apply a =countIF solution stipulating that if the count is greater than 3 and your cells =TRUE then it would highlight, if you can create a demo sheet as I said above with your layout then we can provide a formula based on that. – Manakin Oct 26 '18 at 9:24
2

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

  1. 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).

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

0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.