Count children rows where multiple criteria is met

LSW

I am working on reporting of a tracker to return a "1" in a column for only children rows, where specific criteria are met in other columns in the same sheet. Below is what the AI formula maker returned, but I am getting an error back. Any ideas?

=IF(AND(COUNTIF(CHILDREN([BB Label Approval Status]@row), "complete"), COUNTIF(CHILDREN([SS Approval Status]@row), "complete"), COUNTIF(CHILDREN([BB Specification Updated]@row), "complete"), [New Label Implemented]@row = "Yes"), 1, 0)

Jennifer Kurtz

Just to clarify, you're only looking for a "1" to be returned when several conditions are met? Any chance you're using a Helper column to identify what Level a row is? (That might simplify the formula a bit - if you don't have a column like that yet, you could add one, using the formula =Count(Ancestors()) - anything greater than 0 is a child row!

Then, you could simplify what AI returned, like this:

=IF(AND(Level@row>0, [BB Label Approval Status]@row="complete", [SS Approval Status]@row="complete", [BB Specification Updated]@row="complete", [New Label Implemented]@row = "Yes"), 1, 0)

The formula above, of course, will require that you have a helper column called "Level" where you're calculating the number of Ancestors. it will look for Child rows with the various other criteria you specified.

Does that help?

Ipshita

Hello @LSW , the "complete" is looking for an exact match. Make sure you have complete and not Complete in the columns and also if it is possible for you to share the sheet (remove all data and just share a copy with some test data) and I can check for the error for you

Thanks,

Ipshita

Jennifer Kurtz

Just to clarify, you're only looking for a "1" to be returned when several conditions are met? Any chance you're using a Helper column to identify what Level a row is? (That might simplify the formula a bit - if you don't have a column like that yet, you could add one, using the formula =Count(Ancestors()) - anything greater than 0 is a child row!

Then, you could simplify what AI returned, like this:

=IF(AND(Level@row>0, [BB Label Approval Status]@row="complete", [SS Approval Status]@row="complete", [BB Specification Updated]@row="complete", [New Label Implemented]@row = "Yes"), 1, 0)

The formula above, of course, will require that you have a helper column called "Level" where you're calculating the number of Ancestors. it will look for Child rows with the various other criteria you specified.

Does that help?

LSW

Jennifer - This worked for me. The last thing I needed was another column, but sometimes you just have to do it. Thank you!

Jennifer Kurtz

I hear you on the "another column" thing!! :D But having a "level" column can be super helpful with other stuff, so maybe it will come in handy for other things down the line. ;) Glad you were able to get it working!