SUMIF of CHILDREN on Different Sheet
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.
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!