Two SUMIF Questions: NOT and Children

Robert Casper

=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.

Mike Wilday

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.  

Robert Casper

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.

Mike Wilday

You may not have to, have you tried it? 

Robert Casper

=SUMIF(CHILDREN(Status), <>"Rejected", CHILDREN())

Unfortunately, this kicks back an #UNPARSEABLE error.  I have to use Status3.

Mike Wilday

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? 

J. Craig Williams

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

 

 

Robert Casper

Thanks for all the help.  I've got the items working now