Counting Grand Children - Count of Children

Kaleb

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

Sameer Karkhanis

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)

Sameer Karkhanis

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.

Khasim

Hi @Kaleb , Could you please elaborate on the question, or add an image of what you're attempting to achieve?

Kaleb

https://community.smartsheet.com/discussion/comment/340541#Comment_340541

The Circled Formula in Test Status is Counting the Child too. I Just want it to Count the Grand Children

Sameer Karkhanis

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)

Kaleb

https://community.smartsheet.com/discussion/comment/340648#Comment_340648

Thanks