Syntax help for COUNTIFS with DESCENDANTS

EG_Well

Hi there!

I have two separate COUNTIFS formulas that work individually on their own. However when I try to combine them into a single COUNTIFS formula I get an Incorrect Argument Set error.

Here's what I've got:

Successful Formula #1 - counting the descendants of Row 1 where the Simplified Status column is "Not Started" to get a count of all Phase 1 descendants that haven't been started yet

=COUNTIFS(DESCENDANTS([Simplified Status]1), "Not Started")

Successful Formula #2 - counting the rows where the Count Children column has a value of 0. (Count Children is a helper column that has a formula of =COUNT(CHILDREN()) that I'm using here to filter only the terminal tasks, i.e. the ones that have no children of their own.)

=COUNTIFS([Count Children]:[Count Children], =0)

Now I want to combine these two to count all the terminal descendants of Row 1 where the Simplified Status is "Not Started" so I can count all Phase 1 tasks that haven't been started yet for Roll Up reporting. I basically want to meet the criteria of Formula 1 AND Formula 2. However, combining them keeps throwing an #INCORRECT ARGUMENT SET error:

=COUNTIFS([Count Children]:[Count Children], =0, DESCENDANTS([Simplified Status]1), "Not Started")

I'm sure the answer to this is stupidly simple but where am I going wrong here?

If I nest COUNTIFS without the DESCENDANTS argument, it works fine. The below formula works, however it's not filtering the descendants of Row 1:

=COUNTIFS([Count Children]:[Count Children], =0, [Simplified Status]:[Simplified Status], "Not Started")

---------

Context: I'm trying to get roll up reporting charts for all Phase 1 tasks that are in a given status. This formula is going into a Sheet Summary field. I'm trying to get terminal children for clean numbers. Using the DESCENDANTS function alone means that parents get counted as well, inflating my numbers for actual tasks.

Paul Newcome

It is because your ranges are not the same. In one range you are pulling DESCENDANTS but in the other range you are pulling the entire column. Try changing the reference to the Count Children column to be DESCENDANTS([Count Children]1) so that it matches the other range.

Paul Newcome

It is because your ranges are not the same. In one range you are pulling DESCENDANTS but in the other range you are pulling the entire column. Try changing the reference to the Count Children column to be DESCENDANTS([Count Children]1) so that it matches the other range.

EG_Well

Thanks for the reply Paul! Worked like a charm! For anyone who finds this thread later, here's the final syntax:

=COUNTIFS(DESCENDANTS([Count Children]1), =0, DESCENDANTS([Simplified Status]1), "Not Started")

Paul Newcome

Happy to help. 👍️