How to count children rows with multiple criteria

Paul Dieterich

I have tried this many ways and can't figure it out. The column names I am working with are: "CaseRegNum", "Type of Review", and "Status".

Each parent row is a different year and beneath each year are cases IDs (these are in the caseregnum column). The cases are categorized into "general" and "special" (this is the type of review column) and then each case is given a status (for example "Closed" or Ready for Review").

What I need is to count the "general" cases by their status, only the children of 2018 cases. So, count the 2018 children that are "general" and "Ready for Review"

Any help is appreciated, thanks!

Jason Tarpinian

You would need to use the COUNTIFS function while referencing other columns CHILDREN. Instead of closing the CHILDREN() off right away like you might normally do, reference another column name for your range, and the qualifier for your criterion.

=COUNTIFS(CHILDREN([Type of Review]@row), "General", CHILDREN(Status@row), "Ready for Review")

Jason Tarpinian

You would need to use the COUNTIFS function while referencing other columns CHILDREN. Instead of closing the CHILDREN() off right away like you might normally do, reference another column name for your range, and the qualifier for your criterion.

=COUNTIFS(CHILDREN([Type of Review]@row), "General", CHILDREN(Status@row), "Ready for Review")

Paul Dieterich

Thank you so much! I swear I tried that a hundred times! I appreciate your timely response!

Jennifer Petty

Hello,

I'm in need of a formula to count the number of tasks (children rows) that have been assigned in 2021 only. What would be the best formula for this? I've used a variety of different formulas and keep getting #Unparseable.

Any help would be greatly appreciated!

Candace Haskell

You would need a helper column that pulled out the year (formula: =YEAR([Start Date]@row) ) You can hide this column and then your count formula would pull from that column

Jennifer Petty

Thanks! There's no way to use the existing date ranges (start and due date) to count the number of children that fall between 1/1/2021 and 12/31/2021?