COUNTIFS PARENT cross reference

In my summary sheet, I need to cross reference the PARENT rows in my data sheet (status) to count the number of open non-conformities (with less than 100% complete). Basically, the counting in summary sheet needs to take into consideration two conditions in the status sheet:

  1. Be a PARENT row, and
  2. % complete <1

I have tried everything I can and I still cannot it to work

Best Answer

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Answer ✓

    @Misayely Abias

    Add a "helper" column to your status sheet called IsParent (of type Checkbox) and then put a column formula in it:

    =IF(COUNT(CHILDREN(Item@row)) > 0, true)

    In your summary sheet use the formlua =COUNTIFS({Status Sheet IsParent Column}, true, {Status Sheet % Complete Column}, <1)

    I hope this help.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Answer ✓

    @Misayely Abias

    Add a "helper" column to your status sheet called IsParent (of type Checkbox) and then put a column formula in it:

    =IF(COUNT(CHILDREN(Item@row)) > 0, true)

    In your summary sheet use the formlua =COUNTIFS({Status Sheet IsParent Column}, true, {Status Sheet % Complete Column}, <1)

    I hope this help.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Ranzi, this is just perfect! 👌

    Thank you so much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!