SUMIF of CHILDREN on Different Sheet
I want to create a rollup summary sheet from data on another sheet.
The source sheet/data, let's call it "Pricing":
The summary sheet, lets call it "Summary":
What formula can I use to grab the Cost and Price from each Store # on the Pricing Sheet and automatically populate the Summary Sheet?
I imagine it's some combination of SUMIF and CHILDREN.
Best Answer

I was able to get Heather's solution working with a small modification, it was just missing the criteria.
=SUMIFS({TOTAL}, {STORE #}, {STORE #} = "ATC Cost", {Helper}, {Helper} = [Store #]@row)
I got #INCORRECT ARGUMENT SET on Paul's formulas and I'm not familiar with COLLECT() so I went with the path of least resistance because I know the syntax of SUMIFS().
Thanks!
Answers

You can't use hierarchy functions with cross sheet references, so you will need to add a helper column to your source sheet that replicates the parent row data on each of the child row.
=PARENT([Store #]@row)
Then to pull cost...
=INDEX(COLLECT({Source Sheet TOTAL Column}, {Source Sheet Helper Column}, [Store #]@row, {Source Sheet Store # Column}, @cell = "Cost"), 1)
And price...
=INDEX(COLLECT({Source Sheet TOTAL Column}, {Source Sheet Helper Column}, [Store #]@row, {Source Sheet Store # Column}, @cell = "Price"), 1)

Hi Manuel,
I'm sure someone else may have a more complex way of doing this, but here's my idea:
In the Pricing sheet, I added a helper column to the left of the Store # column. (This column can be hidden.) In that column, I set the column formula to:
=parent([store #]@row)
This pulls the store number listed in the parent row to the new column for each row.
In the Summary sheet, I then used this formula for the Cost column:
=SUMIFS({TOTAL}, {STORE #}, "Cost", {store}, [Store #]@row)
In the Summary sheet, I then used this formula for the Price column:
=SUMIFS({TOTAL}, {STORE #}, "Price", {store}, [Store #]@row)
In these formulas, {TOTAL} is the named reference for the TOTAL column from the Pricing sheet. {STORE #} is the named reference for the Store # column from the pricing sheet. {store} is the named reference for the helper column I added to the pricing sheet.
Hope this helps. Let me know if you have any questions!
Best,
Heather

I was able to get Heather's solution working with a small modification, it was just missing the criteria.
=SUMIFS({TOTAL}, {STORE #}, {STORE #} = "ATC Cost", {Helper}, {Helper} = [Store #]@row)
I got #INCORRECT ARGUMENT SET on Paul's formulas and I'm not familiar with COLLECT() so I went with the path of least resistance because I know the syntax of SUMIFS().
Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!