Check a single box based on multiple Harvey balls

02/23/21
Accepted

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??

Best Answers

  • Heather DHeather D ✭✭✭✭✭
    Accepted Answer

    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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

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

    =IF(COUNTIFS([email protected]:[email protected], "Full") = 10, "Full")

Answers

  • Heather DHeather D ✭✭✭✭✭
    Accepted Answer

    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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

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

    =IF(COUNTIFS([email protected]:[email protected], "Full") = 10, "Full")

  • Got it to work! Thanks!!

  • Heather DHeather D ✭✭✭✭✭

    @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 NewcomePaul 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([email protected]:[email protected], 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.

Sign In or Register to comment.