Using Parent Rows in Reference Another Sheet Formula

Courtney Coules

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?

Paul Newcome

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.

Paul Newcome

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.

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

@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

@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.

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

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

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.