Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Sumif with Children

John Bandy
edited 12/09/19 in Archived 2015 Posts

I want to do a sumif based on the value of one column, but only get the children tasks so the summary tasks are not included in the sum. I can get the sumif to work fine; I can get a sum(children()) to work fine. However I can't find the right syntax for the =sumif(col:col, 1, children())


Does anyone have an example of that kind of formula?



  • Travis
    Travis Employee

    Hi John - Your formula isn’t working because your criteria range (col:col) is looking to the entire column, while sum range is looking only to the children of a parent. These two ranges need to include the same cells. The easiest way to fix this is to replace col:col with a CHILDREN() function. 


    For example, if your parent row is row 5, use this formula:





    This will check all the child rows under col5 for a 1 and will sum the corresponding cells. 

  • John Bandy

    Excellent! Thank you so much! That did it.

  • Dan Prascher

    Really cool function and really handy.


    Is there a way to use it while checking for text? Something like: =SUMIF(CHILDREN(col5), "Active", CHILDREN())

  • Dan Prascher

    This doesn't seem to work anymore. I'm putting the following into Column3, Row2:


    =sumif(children(try), 1, children())



    Am I doing something wrong?  This is the perfect functionality that I need. A little help would be VERY awesome!


  • Dan Prascher
    edited 06/26/16

    Nope, nevermind, I just figured out that Travis meant Row5 instead of Col5 when he said: 




    Got it! Works like a champ and with text matching too. Awesome! This is going to save me tons of time!

  • sparrow

    I am trying to do a similar thing but can't seem to make it work. See snip attached. This formula doesn't seem to work...? Can someone get me a syntax that can be pasted into parent rows that will sum the children correctly based on the check box in column "ready to ship"?


This discussion has been closed.