Sumif with Children

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?

Find more posts tagged with

Formulas

Comments

Travis

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.

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

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!

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"?