summing values across sheets

I have 3 metrics sheets I would like to amalgamate and add sales figures in a central sheet or report.

ie adding sales for the month from multiple locations.


How do I do this as when I use the

=SUMIFS({Quotes Client Master 2020 onward & Confirm Range 4}, {Quotes Client Master 2020 onward & Confirm Range 1}, "Emer", {Quotes Client Master 2020 onward & Confirm Range 3}, "01/03/21")

and change the reference sheet it changes ALL the cells to that reference document ie I cannot have references to multiple sheets.

Excel you can do this - any help would be greatly appreciated


Even if this is possible in dashboards

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Tracey Tume 

    Hope you are fine, you need to create a new formula for each sheet reference because if you use reference update then all the formula in this sheet will update to the new sheet reference. if you like i can help you but i need you to share me as an admin on a copy of your sheet to write the exact formula for you.

    My Email: Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Tracey Tume

    I hope you're well and safe!

    To add to Bassam's excellent advice/answer.

    Depending on your specific structure, it might also be possible to use cell linking instead.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭

    Thanks to you both for your responses  @Bassam Khalil @Andrée Starå

    I have three sheets containing different sales data that I want to combine into one sales figure for the month for each of our 14 sales team members - I will do this on another separate sheet.

    We have used cell linking before but I believe this is for just one cell to one cell not multiple cells to one cell so not sure this will provide me with what I need.

    I had success with the formula below but you have to do this manually in EVERY cell for EVERY month and for EVERY team member. You can do this in excel so easily too!

    Unfortunately, the formula doesn't read the result as a number or dollar amount so you cannot SUM it later on.

    =JOIN({Designer Sales Totals - By Month 2018 - Ad Range 2} + {Designer Sales Totals - By Month 2020 onwa Range 2} + {Designer Sales Totals 2018-2021 - QCM2018- Range 1})

    This is a bit limiting...

    Any help will be gratefully received :)



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Tracey Tume

    To make the result of Join formula a number you need to add VALUE function for your formulas.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Tracey Tume

    Happy to help!

    Cell-linking can be multiple cells, so for example, if you have a row in a project with metrics that can easily be linked to a so-called Master Metrics sheet.

    Make sense?

    Would that work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!