#### 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

Options
edited 12/09/19

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?

Tags:

• Employee
Options

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:

=SUMIF(CHILDREN(col5), 1, CHILDREN())

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

• Options

Excellent! Thank you so much! That did it.

• Options

Really cool function and really handy.

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

• Options

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!

• edited 06/26/16
Options

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

=SUMIF(CHILDREN(col5), 1, CHILDREN())

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

• Options

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.