Check a single box based on multiple Harvey balls

Marisa Jasso

I have multiple rows listing activities for items A through J (arranged as columns). Each item has a Harvey ball to show its completion - e.g., half, three-quarters, full, etc. I would like for a checkbox column to be checked when all items' Harvey balls are full.

What I want to do is this:

=IF([Item A]@row:[Item J]@row = "Full", 1, 0)

But I keep getting INVALID OPERATION. Help??

Heather Duff

Hi Marisa,

You may have to use an AND statement:

=IF(AND([Item A]@row= "Full", [Item B]@row= "Full",[Item C]@row= "Full",[Item D]@row= "Full",[Item E]@row= "Full",[Item F]@row= "Full",[Item G]@row= "Full",[Item H]@row= "Full",[Item I]@row= "Full",[Item J]@row= "Full"),1, 0))

Hope it helps!

Best,

Heather

Paul Newcome

If all of your columns are next to each other, you could also use a somewhat shorter formula and a range.

=IF(COUNTIFS(A@row:J@row, "Full") = 10, "Full")

Heather Duff

Hi Marisa,

You may have to use an AND statement:

=IF(AND([Item A]@row= "Full", [Item B]@row= "Full",[Item C]@row= "Full",[Item D]@row= "Full",[Item E]@row= "Full",[Item F]@row= "Full",[Item G]@row= "Full",[Item H]@row= "Full",[Item I]@row= "Full",[Item J]@row= "Full"),1, 0))

Hope it helps!

Best,

Heather

Paul Newcome

If all of your columns are next to each other, you could also use a somewhat shorter formula and a range.

=IF(COUNTIFS(A@row:J@row, "Full") = 10, "Full")

Marisa Jasso

Got it to work! Thanks!!

Heather Duff

@Paul Newcome, I love that idea! So much cleaner than using AND. I will tuck that gold nugget into my pocket for the future!

Best,

Heather

Paul Newcome

Happy to help. 👍️

@Heather D I try to use this whenever I have more than just a couple of cells to evaluate (if they are all next to each other). Combining them into a single text string and then evaluating said string usually saves quite a bit of typing.

You can also automate what the count should be equal to by using a COUNTIFS(A@row:J@row, OR(@cell = "", @cell <> "")). This will count all cells whether blank or not across the range. This allows flexibility in the sheet layout if you happened to need to delete columns or add more columns (as long as it is not added to or removed from the far left or far right of the range) later on down the road. That way you don't have to manually update that 10.