Counting formula-checked check boxes in sheet summary column - returns Invalid Data type
Hello,
I am working on a project that is counting checked checkboxes that are checked via a column formula. I have tried changing to a COLLECT and a VALUE function but neither seem to work.
Summary Formula currently: =COUNTIF(VALUE([At Risk]:[At Risk]), =1) - Returns Invalid Data Type
Checkbox column formula: =IF(OR((AND([% Complete]@row <= 0.5, NETWORKDAYS(TODAY(), Finish@row) <= 10)), (AND([% Complete]@row <= 0.75, NETWORKDAYS(TODAY(), Finish@row) <= 5)), (AND(Status@row = "Not Started", NETWORKDAYS(TODAY(), Finish@row) <= 5)), AND([% Complete]@row < 1, Finish@row < TODAY())), 1)
Any help would be appreciated!
Best Answer
-
Hey @Mross0878,
In your At Risk column, are there any cells that show "Invalid Data Type"? I just discovered that if a row in the At Risk column has "Invalid Data Type", that also gets sent to the Summary Field.
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
Answers
-
Hey @Mross0878,
I think you're receiving an error because you put the '=' in the criteria for something that's not a number. Checkbox columns just use the 1 and 0 as indicators, not as actual numbers. This should work:
=COUNTIF([At Risk]:[At Risk], 1)Hope this helps!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
Sorry, it didn't fix the issue!
-
Hey @Mross0878,
Weird….And the summary field is a Text/Number field?
I just tried using the formula you provided and I'm getting an unparseable. Is that formula working for you?
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
The Summary is a Text/Number and yes it looks to be working. I did not create the original column formula, I was pulled in after the fact, but it looks to be functioning.
-
Hey @Mross0878,
In your At Risk column, are there any cells that show "Invalid Data Type"? I just discovered that if a row in the At Risk column has "Invalid Data Type", that also gets sent to the Summary Field.
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
Yes there are. Let me review what happened to those and get back to you! I'll respond again if that fixes it!
-
That worked. I added an IFERROR to the column formula to set errors to unchecked. The errors were from dates that have not been entered yet. Thank you for all your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 58 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
