SUMIFS with CHILDREN Help

System

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

DarekJ

Hi,

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 https://app.smartsheet.com/b/publish...

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

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")