summing totals, referencing multiple sheets

thebrinehaus

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.

Sheryl P

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

Andrée Starå

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

thebrinehaus

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.

Paul Newcome

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.

Andrée Starå

Thanks!

I saw that Paul answered.

Did you get it working?

Have a fantastic weekend!

Best,

Andrée