# summing totals, referencing multiple sheets

Options
edited 12/09/19

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.

Tags:

• ✭✭✭✭✭
Options

Check the @row function. There are great resources in the Help & Community areas - Videos and the Sample formula sheet (browse Templates)

• ✭✭✭✭✭✭
Options

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.

• Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Thanks!

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!