How can I count blank or filled in cells?
Ultimate goal: To have status column update to Complete once all cells are filled in
Problem: I'm trying to count 15 cells on a row (not a range), find out if they are all filled in, then once all the selected cells are filled in, the column I'm using for this formula will turn into a check box where I can create automation to have the status turn complete. I was getting the error of #booleanexpected but now that I made it a text column it just says #UNPARSEABLE.
Formulas I've tried: =COUNTIFS(ISBLANK([Strives to Exceed Organizational Goals]@row,(@CELL), [Ensures Accountability]@row,(@CELL), [Makes Informed Decisions]@row,(@CELL)
I've also tried formula =COUNTIFS([Service-Oriented]@row, ISBLANK([Service-Oriented]@row, [Instills Trust]@row, ISBLANK([Instills Trust]@row), [Wows the Customer]@row, ISBLANK([Wows the Customer]@row)))
This is the only way I can figure out how to count blanks or non-blanks then once it is at zero, have automation update status to Complete. (If you have better solution, please LMK)
Answers
-
Hi @MyShell222
I would probably use something like this, just add in the additional column references alongside those in the example formulas below.
=IF(COUNT([Service-Oriented]@row, [Service-Oriented]@row, [Instills Trust]@row, [Wows the Customer]@row, [Strives to Exceed Organizational Goals]@row,[Ensures Accountability]@row,[Makes Informed Decisions]@row) < 15, "False", "True")
This simply counts any completed cells that have anything in them, but if all 15 are not completed it makes your checkbox stay blank, 'False'
Or should also work
=IF(COUNT([Service-Oriented]@row, [Service-Oriented]@row, [Instills Trust]@row, [Wows the Customer]@row, [Strives to Exceed Organizational Goals]@row,[Ensures Accountability]@row,[Makes Informed Decisions]@row) = 15, "True", "False")
This simply counts any completed cells that have anything in them, but if all 15 are completed it makes your checkbox tick, 'True'
Hope that helps
Thanks
Paul
-
Try this:
=IF(COUNTIFS([First Column Name]@row:[Last Column name]@row, @cell <> "") = 15, 1)
-
Hi @Paul Newcome - the first formula of yours works however it's not checking the boxes:
Formula: =IF(COUNT([Service-Oriented]@row, [Service-Oriented]@row, [Instills Trust]@row, [Wows the Customer]@row, [Strives to Exceed Organizational Goals]@row, [Ensures Accountability]@row, [Makes Informed Decisions]@row, [Demonstrates Agility]@row, [Embraces Ambiguity]@row, [Drives Continuous Improvement]@row, [Communicates Openly]@row, [Builds Relationships]@row, [Values Learning & Growth]@row, Innovates@row, [Exhibits Courage]@row, [Connects the Dots]@row, [Funct Knowledge]@row, Application@row) < 17, "False", "True")
Thoughts?
-
@MyShell222 Try using a 0 (zero) for unchecked and a 1 for checked.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!