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

Options
Robert Casper
Robert Casper ✭✭
edited 12/09/19 in Archived 2017 Posts

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

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Mike Wilday ✭✭✭✭✭✭
    Options

    You may not have to, have you tried it? 

  • Robert Casper
    Options

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

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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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

     

     

    SUMIF_example.jpg

  • Robert Casper
    Options

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

This discussion has been closed.