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.
Two SUMIF Questions: NOT and Children
=SUMIF(CHILDREN(Status3), "Rejected", CHILDREN())
1) How do you use the NOT logical expression in a SUMIF statement to add values together only in those rows whose value does NOT have a certain string? For example, add all of the cost values together unless the status says "Rejected".
2) For the condition test range of the SUMIF Statement, when referring to children under a given column, do you have to use the row number? Trying to figure out why I need to use Children(Status3) instead of Children(Status).
Man, I wish this had an expression builder. I'd pay extra for it.
Comments
-
1. I think you would <>"Rejected" which means, in essence, greater or less than the "Rejected" - basically anything but that.
2) If you don't limit it to a particular row, then it would pull all children from all parents. Not sure if that would logically work for smartsheets. Have you tried it? I don't have a sample to work with, but see if it works? Using the row# will limit it to a particular grouping of items.
-
The <> worked on the first piece, thanks!
As to the Children item, normally in Smartsheet you can go without referencing a row number if you are writing a formula in the parent row. Not sure why I have to reference the row number when I specify a given column.
-
You may not have to, have you tried it?
-
=SUMIF(CHILDREN(Status), <>"Rejected", CHILDREN())
Unfortunately, this kicks back an #UNPARSEABLE error. I have to use Status3.
-
If you copy the cell, without double clicking into the cell, and paste it into a new row, it will update the row number automatically in the formula. Is that what you were looking to ignore the row number for?
-
Robert,
To explain the formula:
This part
CHILDREN(Status3) is reference to the children of the [Status] column, row 3.
CHILDREN expects a cell reference to the parent cell. It allows () which means "my cell"
When you tried
CHILDREN(Status) you are expecting it to understand that you mean the cell on the same row. But that is an assumption that the tool does not (and should not, in my opinion) make.
There could be some argument that SUMIF may imply references to a row, but that is actually not true.
The image below has a formula that has a range of 3 cells for the criterion check and cells to sum that are not on the same row. As long as the range size is the same, the formula should return the values expected.
Craig
-
Thanks for all the help. I've got the items working now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives