Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Trying to Sum Rows of linked cells

Buddy Bigelow
edited 12/09/19 in Archived 2017 Posts

I'm new to Smartsheet and struggling with a few things, one in particular is wearing me out. I have several rows of linked cells that pull number values from another Sheet. I'm trying to Sum total these linked cells but the basic Sum formula doesn't seem to recognize these numbers. If I remove the Link the Sum formula works as expected. Am I doing something wrong or should I be doing it another way?

 

Thank you in advance.

Capture.JPG

Comments

  • Taylor F
    Taylor F Employee Admin
    edited 03/03/17

    Hello Buddy, 

     

    Welcome to the Smartsheet Community! Sometimes when a Cell Link brings over a value it becomes a text value instead of a numeric value. 

     

    Rather than using the SUM function, you can add each cell together by string them together with the addition symbol (+) and place the cell refernces inside of the VALUE function which will convert it back to a numeric value.

     

    For example:

     

    =VALUE([Shift 1 Filled]1) + VALUE([Shift 1 Filled]2) + VALUE([Shift 1 Filled]3) + VALUE([Shift 1 Filled]4)

     

    NOTE - I used Shift 1 Filled as it is show in your image but its not the header name. You will need to use the column headers and row numbers from your sheet. 

     

    If this doesn't resolve the issue, please contacts us by filling out the Contact Us form on our Help site and we will be happy to troubleshoot this further. 

     

    -Taylor

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Buddy,

     

    VALUE is the way to go.

    I would do this slightly differently.

     

    1. Add new column

    2. In that column, have your conversion to number

    =VALUE([Shift 1 Filled]1)

    3. Sum in that column

    4. And then grab that value back into your linked column

    =[New Column]25

     

    The new column can be hidden.

     

    This will increase the overhead slightly (because of blank cells, mostly) but will save on typing and maintenance cost (to add a row in the group is easy and you likely won't even need to unhide the column)

     

    Craig

  • Thank you both for the responses! After some head scratching I realized the linked cells in the pic I provided were pulling from a Column Type that was a date...This row was the first row in that column and that's why it wouldn't sum. After moving it to a generic Text/Number column it worked fine. 

     

    Again thanks for the responses!

This discussion has been closed.