Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Percentage of Child Rows with Check Mark
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?
Comments
-
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)
-
This formula
=(COUNT(CHILDREN(), 0) + ""
is incorrect (it is off by 1)
=COUNT(CHILDREN()) + ""
is.
Craig
-
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) + "")
-
J.Craig,
=(COUNTIF(CHILDREN(), 1) + "") / (COUNT(CHILDREN()), 0) + "")
I used this, but it's not working.
-
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
-
=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.
-
-
This gives me a #Divide By Zero error.
-
If anyone wants to take a try at it, here you go:
<IFRAME WIDTH=1000 HEIGHT=700 FRAMEBORDER=0 SRC="https://app.smartsheet.com/b/publish?EQBCT=bd662b969cf048708c879cd9ae8ba4d8"></IFRAME>
-
In a Checkbox column?
It should not if you have children (checked or unchecked).
Craig
-
Fixed. You had no children (rows need to be indented)
Craig
-
I just saw that and changed it, and it works.
Thank you so much J.Craig!
-
The result reports as .25 instead of 25%.
Do you have any thoughts on fixing this?
-
Highlight the cell and click the little % up by the dollar sign in the top bar
-
That doesn't work.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
