Counting Grand Children - Count of Children
Smartsheet Community:
I am not Sure where to Put this Part of the Formula in my Existing Formula to Subtract The Children.
Existing Formula:
=VALUE(COUNTIF(DESCENDANTS([Test Status]@row), "Pass")) + " Passed Out Of " + VALUE(COUNTIFS(DESCENDANTS([Test Status]@row), OR(@cell = "", @cell <> "")))
Formula To Add: To Subtract The Children
-COUNT(CHILDREN())
Thanks
Best Answer
-
How many other values does the Test Status column have other than "Pass"? If it is limited to a few, say under 5 or so (I would assume Pass/Fail/In-Progress/Not Started) then it will be easier to calculate without a helper column. The reason you are getting additional count is because in the second COUNTIF function you are looking for all descendants that have any value (aka @cell <> ""). Instead if you consider only those rows which have any of those restricted Test Status values then it will give you total count you are looking for using the formula,
=COUNTIF(DESCENDANTS([Test Status]@row), "Pass") + " Passed Out Of " + COUNTIFS(DESCENDANTS([Test Status]@row), OR(@cell = "Pass", @cell <> "Fail", @cell = "In-Progress", @cell = "Not Started"))
Alternatively, you can add a helper column with the simple formula =COUNT(DESCENDANTS()) and convert it to column formula and hide the column. Then use the helper column in the formula as,
=COUNTIFS([Test Status]:[Test Status], "Pass", [Helper Column Name]:[Helper Column Name], 0) + " Passed Out Of " + COUNTIF([Helper Column Name]:[Helper Column Name],0)
Answers
-
If you can share a screenshot of your sheet there might be a better way to achieve what you are trying to get to as it is not clear from the formula.
-
Hi @Kaleb , Could you please elaborate on the question, or add an image of what you're attempting to achieve?
Thanks & Regards
Email ID: info@sspmconsultants.com
Did I answer to your question or fix the problem? Please
help
theSmartsheet Community
by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering! -
The Circled Formula in Test Status is Counting the Child too. I Just want it to Count the Grand Children
-
How many other values does the Test Status column have other than "Pass"? If it is limited to a few, say under 5 or so (I would assume Pass/Fail/In-Progress/Not Started) then it will be easier to calculate without a helper column. The reason you are getting additional count is because in the second COUNTIF function you are looking for all descendants that have any value (aka @cell <> ""). Instead if you consider only those rows which have any of those restricted Test Status values then it will give you total count you are looking for using the formula,
=COUNTIF(DESCENDANTS([Test Status]@row), "Pass") + " Passed Out Of " + COUNTIFS(DESCENDANTS([Test Status]@row), OR(@cell = "Pass", @cell <> "Fail", @cell = "In-Progress", @cell = "Not Started"))
Alternatively, you can add a helper column with the simple formula =COUNT(DESCENDANTS()) and convert it to column formula and hide the column. Then use the helper column in the formula as,
=COUNTIFS([Test Status]:[Test Status], "Pass", [Helper Column Name]:[Helper Column Name], 0) + " Passed Out Of " + COUNTIF([Helper Column Name]:[Helper Column Name],0)
-
Thanks
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives