Using Parent Rows in Reference Another Sheet Formula

I'm attempting to calculate the total number of pediatric calls by county using this formula all referencing another sheet:

=SUMIF(PARENT({MAHEC County}, "Buncombe"), {Pediatric Calls})

However, it is not working. Any suggestions?

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    We can't use hierarchy based functions with cross sheet references. You will need to insert a helper column on the source sheet (can be hidden after setting up) that pulls the parent row data onto every row. Then you would reference this column in your formula with the cross sheet reference.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    We can't use hierarchy based functions with cross sheet references. You will need to insert a helper column on the source sheet (can be hidden after setting up) that pulls the parent row data onto every row. Then you would reference this column in your formula with the cross sheet reference.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Courtney Coules Looks like the below formula will accomplish what you are trying to do.

    =SUMIF({MAHEC County}, PARENT({MAHEC County}) = "Buncombe", {Pediatric Calls})

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Paul Newcome

    Hi Paul, I created two sheets and tested the formula I posted and it did work for me. Would be curious to see if you get the same result.

    I had a project sheet with Buncombe on the Parent Task and created 2 subtasks under it named task1 and task2. Then I created a column called numbers and put a number value in every cell. From there, I created a metric sheet and used my formula.

    {Mahec County} = Project Sheet Task Name Column

    {Pediatric Calls} = Project Sheet Numbers Column

    When I changed the numbers my sumif updated accordingly.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @JamesB Can you provide screenshots? I have tried it a few different ways including the same way you have it, and am unable to get it to work. In my below screenshots, I would expect an output of 2, but I get a 1 with your method (as if it is ignoring the PARENT function altogether) and an expected #UNPARSEABLE when using "@cell" references which would be the expected syntax.




    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Paul Newcome

    After reading your comments and reviewing my setup, I realize that I was getting a false positive on my equation. Because my numbers column was getting a total count from its children, and my sumifs was looking for the name buncombe, and getting the results from the adjoining cell in the numbers column the parent formula section of my logic was basically being ignored, it was still looking at every row in the reference. (Interesting that it was not returning an error). As soon as I named one of the subtasks the same as the parent it got added to the sumifs total, thus making the number higher than actual.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @JamesB So while not ideal, at least we are both getting the same outputs.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Paul Newcome

    Agreed. This is a good post for the community though to assist others in understanding that a false positive could be generated. At least until hierarchal formulas work in cross sheet references.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!