Need help getting a percentage of checkboxes
I have a column with check boxes. I want to get a percentage for total number of boxes that are checked vs the total number of boxes (checked or unchecked). I want this in the parent row to count the children rows only. I got the below formula to work however when I go to use the percentage in a pie chart it wont accept the data. I suspect this is because Smartsheet somehow doesn't see it as a number but a text format? I know that unchecked equals 0 and checked equals 1. Just not sure how to program it.
=COUNTIF(CHILDREN(), true) / COUNT(CHILDREN()) * 100 + "%"
Answers
-
Format your column as percentage so you dont have to add the percent sign.
...
-
@heyjay so would the formula be =COUNTIF(CHILDREN(), true) / COUNT(CHILDREN()) * 100
If so I tried that and got "BOOLEAN EXPECTED"
-
=COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN())
This should give you a decimal as a result, then using the % format will convert everything.
For the check boxes, it should be 1 or 0 for true or false. I cant find the documentation, but it should be something like that. If that doesnt work, please try to enclose 1 in quotes, "1".
...
-
@heyjay got "BOOLEAN EXPECTED" again. Tried with and without quotes and made sure the column was set to percentage.
-
Can you please provide a screenshot or a sample of how your sheet looks like?
Your
CHILDREN()
should have a reference inside.CHILDREN(COLUMN_NAME:COLUMN_NAME)
Are you also making your formula on a column that is a check box properties? That is what the error is about. Its expecting 1/0 result, otherwise it'll return that error. https://help.smartsheet.com/articles/2476176-formula-error-messages#:~:text=of%20the%20problem.-,%23BOOLEAN%20EXPECTED,-Cause
...
-
=COUNTIF(CHILDREN([NEW**Coordination Signed Off]:[NEW**Coordination Signed Off]), 1) / COUNT(CHILDREN([NEW**Coordination Signed Off]:[NEW**Coordination Signed Off]))
You enter this formula on a column that is formatted as text/number, or in you sheet summary, I assume.
...
-
No I was entering it in the parent row in the checkbox column. Does this mean it cant be in the checkbox column to work?
-
Correct, entering a formula should only result in 1 or 0, check or uncheck, otherwise you'll get that error.
...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!