Column letters? **DUMB QUESTION ALERT!**

Options

Ok... I just went to go put a formula in on my smartsheet and realized.... the columns don't have letters! (this is a sheet created from a form, so the column headers are titles instead... see my screenshot). what do I do? I'm used to doing a super simple equation like =sum(A1:A5)

BONUS question... I want the contents of a column to constantly add up and put a total below the bottom cell in the row. this is an expense report so there will always be more rows added and I want that total to keep moving down the spreadsheet (and also continue to have a running total). Anyone know how to accomplish this?


Answers

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

    Hi Julie,

    You'd replace the letters with the column names instead.

    Example:

    Try something like this.

    =SUM([$ AMOUNT]1:[$ AMOUNT]10)

    You can use either sum at the top or the bottom or in the Sheet Summary section.

    Smartsheet works best when you sum or calculate at the top.

    If you want the sum to be at the bottom, you'd need to change, so the form submissions get added at the top.

    Make sense?

    Would that work?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer! 

    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.

  • For both questions I've found the easiest solution to be integrating the Sheet Summary functionality.

    The sheet summary is a good place to put data which doesn't fit well into specific column, but you still want to dynamically update as changes are made to the sheet base data. The sheet summary tab is found along the right hand side of the smart sheets page when you have an active sheet open.

    From there, create a new field (and rename as necessary i.e. "Q2 2018 Cost Sum").

    Next, input the formula like you would expect, such as:

    =sum([ColumnName]row#:[ColumnName]row#)

    =sum([ColumnName1]row#,[ColumnName2]row#) --- this version sums 2 cells in different columns

    For referencing column names, if the column header does NOT contain spaces it can be directly referenced without the brackets like shown above. For column headers with spaces, include the brackets around the case-sensitive column header name.

    I believe smartsheets has a current row limit of 5000, so having your sheet summary field look like

    =sum(ExampleColumnName1:ExampleColumnName5000)

    should capture all the values in the column "ExampleColumnName".


    From there you can always see the updated sum on the sheet summary tab, or if you use dashboards you can have those dynamically pull the updated sheet summary values into a widget of your choosing.

    -Austin