Parent Sum IF

CTSr
CTSr ✭✭
edited 12/09/19 in Formulas and Functions

I've tried a variety of calculations, and can't seem to get the results I'm seeking.  I'm simply trying to add up all of the parent rows in a column called Resource Days that are Assigned To a specific team member.  Any suggestions?

=SUMIFS(PARENT(Resource Days:Resource Days), [Assigned To]:[Assigned To], "Team Member")

 

 

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 02/06/18

    You don't use PARENT in this case. 

    Column names with spaces (or special characters) need square brackets []

    =SUMIFS([Resource Days]:[Resource Days], [Assigned To]:[Assigned To], "Craig Williams")

    However ...

    Unfortunately, I don't think you can do it in a single formula, so:

    1. add Checkbox column (I named mine [Is Parent])

    2. In that column (row 1 and then copied to the rest of the column)

    =COUNT(CHILDREN([Assigned To]1)) > 0

    or

    =IF(COUNT(CHILDREN([Assigned To]1)) > 0,1,0)

    if that makes you feel better.

    3. Your formula then becomes

    =SUMIFS([Resource Days]:[Resource Days], [Assigned To]:[Assigned To], "Craig Williams", [Is Parent]:[Is Parent], 1)

    I hope that helps.

    Craig

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!