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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!