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, [email protected])
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:[email protected]  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:[email protected]  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!