sumif function across multiple sheets

I am trying to build a roll up sheet that pulls criteria from multiple sheets, into one master document. I keep having an issue when I update the formula range from one sheet, it updates all the ranges when I don't want it to do that.

Here is an example:

For each FFE column, I have a formula specific to each Item group (ie: duvet cover) . I want to total those up from each sheet (SEA FFE for example) into each specific row, and then copy that formula over to the next column (ie. BOS 2 FFE) and have it update the ranges. However, when I copy over the formula and update the ranges, it updates ALL of them on the sheet. It seems crazy that I would have to manually update each cell when i should just be able to update the range to find the next FFE sheet (MIN, KC, and beyond).

Formula example:


Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @JennS_

    Hope you are fine if i understand your question you create a formula to sum the item for example in sheet SEA FFE using the criteria in each row then you are coping the same formula to do the same for sheet BOS 2 FFE for the same criteria.

    if this is the case then for the new sheet formula "BOS 2 FFE" don't use the command "Edit Referance" when you try to refer to the new sheet "BOS 2 FFE" because it's editing the same reference you used for the sheet "SEA FFE".

    instead of that for sheet "BOS 2 FFE" keep the same formula but delete only the reference then add new referance:

    the following is the sample formula:

    for sheet SEA FFE = SUMIFS({SEA FFE}, HAS(@cell, "Roadshow"))

    after you copy the formula to BOS 2 FFE = SUMIFS({BOS 2 FFE}, HAS(@cell, "Roadshow"))

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • JennS_
    JennS_ ✭✭✭

    This did not work for me. Am I selecting the cell from BOS 2? Or am I referencing the sheet it is pulling from? It is giving me invalid reference. Note that each of these cells is pulling from a different sheet and summing up the cost.


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 06/29/21

    @JennS_

    If you can share me as an admin on a copy of your sheet after you remove any sensitive data I will create the exact formula for you then you can copy it to your original sheet

    My Email: [email protected]

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • JennS_
    JennS_ ✭✭✭

    Ok- I just shared it to you. Thank you!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 06/30/21

    @JennS_

    you only shared the main sheet i don't have access to the intake sheets to check your references if you can create a sample workspace and add 2 intake sheets and the summary sheet so we can check what exactly the problem you are facing.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!