Countifs with nested Children

dougo

I am attempting to provide a count against 2 columns of children.

I have an Open or Closed in a Status column and I have 7 references (Technical, Programming, etc.) in a Type column.  I have multiple Parents (these are room locations) that I need unique roll-ups per room (parent).  So, I am using the Children qualifier to just get the entries under that room.

So, I would like to parse the Type Column for the word "Technical" and "Open" for one count. The formula is in the Status Column so I am using Children() and then reference in the Type Column as Children (Type:Type).   

This is what I have as a current formula and it returns an INCORRECT ARGUMENT SET

    =COUNTIFS(CHILDREN(), "Open", CHILDREN(Type:Type), "Technical")

I have tried the below formula with similar error message return.

    =COUNTIFS(CHILDREN(), "Open", Type:Type, "Technical")

Help or suggestions are appreciate.

Paul Newcome

Since you have the formula in the Status column, but you are wanting to countifs in the Type column for that specific parent row, you will need to specify which children to look at.

 

CHILDREN(Type@row) will look at the children for that row only.

 

CHILDREN(Type:Type) will try to look at ALL children in that column.

 

I would also suggest using CHILDREN(Status@row) when referring to the status column simply to avoid any confusion.