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

  • Manuel Wanskasmith
    edited 04/10/21 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

  • Paul Newcome
    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
    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
    edited 04/10/21 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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!