Percentage of Child Rows with Check Mark

craigwaco

Hi,

I'm trying to get a Percentage of Child Rows with Check Mark. Can anyone help out?

=COUNTIF(CHILDREN(), 1) + ""

returns the count of checked boxes.

=(COUNT(CHILDREN(), 0) + ""

returns the count of total rows, check or unchecked.

So I divided the two, but I get #INVALID OPERATION error.

Any thoughts?

Find more posts tagged with

Construction

Formulas

Project Management

Comments

L_123

you are converting the value inside of the cells into a text value by adding the space. I assume you are doing that to format the text to the center. Instead you should use the formatting tools. If you really feel the need to keep the space there you could probably get away with using the value() formula, but it's not really recommended in this case.

=Value(Aconex@row)/value(Procore@row)

J. Craig Williams

This formula

=(COUNT(CHILDREN(), 0) + ""

is incorrect (it is off by 1)

=COUNT(CHILDREN()) + ""

is.

Craig

craigwaco

Thanks Luke,

I'm not trying to divide Aconex into Procore.

I'm trying to Divide the number of checked Aconex boxes into the total number of Aconex boxes.

=(COUNTIF(CHILDREN(), 1) + "") / (COUNT(CHILDREN(), 0) + "")

craigwaco

J.Craig,

=(COUNTIF(CHILDREN(), 1) + "") / (COUNT(CHILDREN()), 0) + "")

I used this, but it's not working.

J. Craig Williams

Use Luke's suggestion of VALUE() to get your text into numbers.

Remove the trailing 0 from your COUNT formula.

If you are putting this formula in a Checkbox column, you need to convert it back to Text again (or skip it if you are replacing your current counts 4 and 16)

=(COUNTIFS(CHILDREN(),1) / COUNT(CHILDREN())) + ""

Craig

L_123

=COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()) + ""

Thats a neat trick using the quotes to change the format to be acceptable to a boolean column. Hadn't seen that before.

Try that formula. It seemed to work for me when I tested it.

L_123

Hah

craigwaco

This gives me a #Divide By Zero error.

craigwaco

If anyone wants to take a try at it, here you go:

J. Craig Williams

In a Checkbox column?

It should not if you have children (checked or unchecked).

Craig

J. Craig Williams

Fixed. You had no children (rows need to be indented)

Craig

craigwaco

I just saw that and changed it, and it works.

Thank you so much J.Craig!

craigwaco

The result reports as .25 instead of 25%.

Do you have any thoughts on fixing this?

L_123

Highlight the cell and click the little % up by the dollar sign in the top bar

craigwaco

That doesn't work.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…