Count Grand Children Only with Verbiage

Anthony Barthelemy

Smartsheet Community:

I ma getting the Right Count (16). However, the last Piece of the Formula is not Displaying "Total Sites Done". I tried adjust my Parenthesis but the count becomes incorrect (14 instead of 16).

Current Formula:

=" AsOne Invoice #1: " + (COUNT(DESCENDANTS()) - COUNT(CHILDREN() + " Total Sites Done"))

Current Result

AsOne Invoice #1: 16

Thanks

Kelly Moore

Hey @Anthony Barthelemy

Try this

=" AsOne Invoice #1: " + VALUE(COUNT(DESCENDANTS()) - COUNT(CHILDREN())) + " Total Sites Done"

Does this work for you?

Anthony Barthelemy

The Count is Still incorrect.

The Grand Children's Count is 15

Plus 3 Children

The Total Should Be 18

The Result is Correct with the Formula Below, but I do not Get the Total Sites Done as Part of the Result, Just AsOne Invoice #1: 18

=" AsOne Invoice #1: " + (COUNT(DESCENDANTS()) - COUNT(CHILDREN() + " Total Sites Done"))

Kelly Moore

In my formula I used the VALUE function to help Smartsheet differentiate the text portion and value portion of the formula. I encourage you to copy paste my formula exactly.

What value should be added for ‘total sites’? You have only indicated a text statement, thus it seems like another data field is needed. A screenshot of your sheet will help me and the community help you build the formula you’re looking for

Kelly

Anthony Barthelemy

I Copied your Formula, but the count is still wrong as I described Above it Should be 18 not 15

Kelly Moore

The Descendants are giving you a total - I believe the correct total. The formula I provided above appears to give a total of 18 Then subtracts 3 children - the subtraction comes from your original formula - to give a final result of 15. Remove the children() term from the equation and see if that's the right count. If you that isn't correct, please provide a screenshot or a mockup of your data so I can help provide the best formula that solves your problem. It's very difficult for the community to help without screenshots.