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?
Best 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.
Answers

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.

@Courtney Coules Looks like the below formula will accomplish what you are trying to do.
=SUMIF({MAHEC County}, PARENT({MAHEC County}) = "Buncombe", {Pediatric Calls})

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.

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

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.

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

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
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!