Need help getting a percentage of checkboxes

Matt RobertP

 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 + "%"

heyjay

Format your column as percentage so you dont have to add the percent sign.

Matt RobertP

@heyjay so would the formula be =COUNTIF(CHILDREN(), true) / COUNT(CHILDREN()) * 100

If so I tried that and got "BOOLEAN EXPECTED"

heyjay

 =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".

Matt RobertP

@heyjay got "BOOLEAN EXPECTED" again. Tried with and without quotes and made sure the column was set to percentage.

heyjay

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

Matt RobertP

heyjay

=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.

Matt RobertP

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?

heyjay

Correct, entering a formula should only result in 1 or 0, check or uncheck, otherwise you'll get that error.