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
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?
Comments
-
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.
-
Excellent! Thank you so much! That did it.
-
Really cool function and really handy.
Is there a way to use it while checking for text? Something like: =SUMIF(CHILDREN(col5), "Active", CHILDREN())
-
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!
-
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!
-
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"?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives