summing totals, referencing multiple sheets

Hello, everyone. I am using Smartsheet to organize our company's sales projections. Each of our product Distributors (9 total) have their own sheet, withΒ case sale projections by Product and Month. I am interested in making a summary sheet, that pulls the case totals from each Distributor sheet, for each Product, again by Month.
I started to use a =SUM formula to reference each Distributor sheet to get my case totals for each Product on my summary sheet, but quickly realized that dragging/copying the formula was not going to work, meaning I would have to do the =SUM formula for each cell (each Product, each Month).
I know there has to be a better way than to write this formula almost 500 times! Any insight as to the correct formulaΒ to use would be much appreciated.
Comments
-
Check the @row function. There are great resources in the Help & Community areas - Videos and the Sample formula sheet (browse Templates)
-
Hi,
Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help.Β (share too,Β andree@getdone.se)
Have a fantastic week!
Best,
AndrΓ©e StarΓ₯
Workflow Consultant @ Get Done Consulting
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.
-
Hi Andree, I have attached two screenshots. I'm showingΒ my Summary sheet, where I am trying to total the case sales for all products, for each month of the year. The total case sales is a combination of sales to each of our 9 distributors. You'll see the =SUMΒ formula I tried to use in one of the screenshots.
Each of the distributor sheets that I am referencing looks exactly the same as the summary sheet shown here.
-
Try using a series of SUMIFS along the lines of...
Β
=SUMIFS({Sheet 1 Sum Range}, {Sheet 1 Primary Column Range}, [Primary Column]@row) +Β SUMIFS({Sheet 2Β Sum Range}, {Sheet 2Β Primary Column Range}, [Primary Column]@row) +Β SUMIFS({Sheet 3Β Sum Range}, {Sheet 3Β Primary Column Range}, [Primary Column]@row) + .....................
Β
Granted this will take a while to do 2 cross sheet references for each of the 9 pages, but you will be able to dragfill on down for the rest of them once you get the initial formula established.
-
Thanks!
I saw that Paul answered.
Did you get it working?
Have a fantastic weekend!
Best,
AndrΓ©e
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
Categories
Check out the Formula Handbook template!