SUMIF with CHILDREN, if greater than 0

r0plbghdv
r0plbghdv
edited 02/02/24 in Formulas and Functions

I am just migrating to Smartsheet from Excel. Smartsheet is missing some basic things, but I'm trying it primarily because of the Indent/Children functions, which are something I love.

I've got some data in, and the =SUM(CHILDREN()) function works well, but I don't want a bunch of 0's all over my sheet, so I've been trying to get a SUMIF working with CHILDREN. I've tried countless combinations of formulas but I'm looking for something like this.

=SUMIF(CHILDREN()>0, "SUM(CHILDREN()", "")

Basically, I only want the SUM of the CHILDREN if the sum is greater than 0, otherwise I just want the cell blank.

Is this possible?

Best Answers

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    Answer ✓

    Hello @r0plbghdv

    Something like this perhaps?

    =IF(SUM(CHILDREN()) > 0, SUM(CHILDREN()), "")

  • John_Foster
    John_Foster ✭✭✭✭✭✭
    Answer ✓

    Hi @r0plbghdv,

    I had some issues with this, but rather than a SUMIF I have used an IF statement which then counts the child rows beneath a row.

    Have a look at this formula and see if it does what you want. You will need to replace the column reference in it.

    =IF(COUNT(CHILDREN([Primary Column Name]@row)) <> 0, COUNT(CHILDREN([Primary Column Name]@row)), "")

    Hope this helps!

    John

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!