I am needing help on a CountIfs Formula when referencing a parent row

Options

I have this table that I am trying to count when a certain college has been completed if it has a signed partnership (referencing the parent row). I am using this formula in the Summary Sheet. Does anyone have any idea how I can make this work? I am getting the following error when I try my formula: #UNPARSEABLE

=COUNTIFS(Status:Status, "In Progress", Alabama:Alabama, "IT College", MOU:MOU, PARENT("Signed Partnership"))

Where am I going wrong in this formula?

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    Have you tried:

    =COUNTIFS(Status:Status, "In Progress", Alabama:Alabama, "IT College", MOU:MOU, PARENT(@cell) = "Signed Partnership")

  • MoriahBev
    Options

    I tried it and am still getting Unparseable

    =COUNTIFS(Status:Status, "In Progress", Alabama:Alabama, "IT College", MOU:MOU, PARENT(@row) = "Signed Partnership")

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    The real holdup here is the fact that the MOU only exists on the parent row. Is there a reason that it can't be copied down to the child rows? It would make the counts much easier. In a test sheet I was playing in I used this formula to copy the parent to all of the children by pasting this formula in all of the child rows.

    =IF(COUNT(ANCESTORS()) > 0, PARENT(), 0)

  • MoriahBev
    Options

    Thank you, it just looks more bulky when it is in all of the rows, but I think that is going to be the best way to get it done. I appreciate the help!

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    No problem. There still might be a way to get the formula to work with looking at the parent, but sometimes the helper columns are very convenient.

    Could you potentially have a helper column that contains all of the copies of the parent and then hide it on the sheet but reference it with your formula?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!