summing totals, referencing multiple sheets

03/19/19 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.

Comments

  • Sheryl PSheryl 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å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, [email protected])

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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.

    2019-03-20 (1).png

    2019-03-20.png

  • Paul NewcomePaul 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.

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Thanks!

    I saw that Paul answered.

    Did you get it working?

    Have a fantastic weekend!

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.