summing values across sheets

Tracey Tume

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

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

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!

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

@Tracey Tume

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

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?