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.
