Check a single box based on multiple Harvey balls
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
-
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
-
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")
Answers
-
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
-
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")
-
Got it to work! Thanks!!
-
@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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!