This discussion was created from comments split from: Sumifs and Children.


  • DarekJ


    It looks like I am having similar issue, but the formula is not working for me...

    I should start by saying that I am learning how to use this tool along with its formulas. However, I have encountered an issue that I cannot solve with the information found on the internet, therefore I am asking for some support here. I would really appreciate any help with this.

    I have created dummy data to reflect my issue, which must be easy to solve - I am sure of that 

     Here it is

    Can anybody look at and help what I am doing wrong?

    I am getting "INVALID OPERATION" when I am trying to use SUMIFS formula.

    This is my formula: =SUM(CHILDREN()) - SUMIFS(CHILDREN(Sep@row), CHILDREN(Role@row), @cell = "GAP Asso", CHILDREN(Sep@row), CHILDREN(Role@row), @cell = "GAP Asis")

    My goal is to find the sum of the parent row in column Sep (then Oct, Nov and Dec , but deducting all values when it says "GAP Asis' or "GAP Asso" in column Role. And I'd like to have this formula for each parent row "Study 1", "Study 2" and so on...

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The second "CHILDREN(Sep@row)" is what is causing the error. Removing it should clear things up for you.

    =SUM(CHILDREN()) - SUMIFS(CHILDREN(Sep@row), CHILDREN(Role@row), @cell = "GAP Asso", CHILDREN(Role@row), @cell = "GAP Asis")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!