SUMIF of CHILDREN on Different Sheet

Manuel Wanskasmith

I want to create a roll-up 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.

Manuel Wanskasmith

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!

Paul Newcome

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)

Heather Duff

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

Manuel Wanskasmith

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!