Format SUM function in a report

Hello,

I have a report with a "simple" SUM function using the following:

But the result shows as follows, without a dollar sign and missing a coma at the thousand point:

How can I format it to show:

$177,682


Thanks!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @TaliRXM

    Thank you for this screen capture! The reason why clicking on the cell brings in the right information whereas typing it doesn't work is because your column name has a space between letters. Any column names that have spaces or numbers need to be written within [these] so the formula knows when the name starts and stops.

    Try this:

    =SUMIFS([Payment status]:[Payment status], Institution:Institution, <> "Stanford")

    Also note that there is no space between Institution:Institution

    See: Create a Cell or Column Reference in a Formula

    Cheers!

    Genevieve

«13

Answers

  • Krissia B.
    Krissia B. Moderator

    Hello @TaliRXM

    At this time, formatting in that section in a report is currently not supported. Please submit an enhancement request to have this feature be considered for the future development of Smartsheet.


    Cheers!

    Krissia

  • TaliRXM
    TaliRXM ✭✭✭

    Thank you. Disappointing.

    Will do


    Tali

  • TaliRXM
    TaliRXM ✭✭✭

    One more thing: can I format it once it is on Dashboard (via using a Widget)?

    It doesn't look like this is an option as well


    Thanks

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TaliRXM

    While the Summary row in a Report can't be manually selected and formatted (either in the Report or in a Widget), it should reflect the formatting of the column it is Summarizing, as long as the formatting has been applied to the entire column in all of the source sheets, like so:


    If even one cell in the referenced column doesn't have the correct formatting applied, then the Report will default to the original value. However if entire column has Currency and Thousand formatting, you should see something like this:

    Let me know if applying formatting to the source sheet(s) has resolved your issue!

    Cheers,

    Genevieve

  • TaliRXM
    TaliRXM ✭✭✭

    Hi @Genevieve P. Thanks!

    Nope, it doesn't work. I had it formatted bur re-did it save and refreshed.

    Although the cells look like:


    The SUM still shows with no $ sign and no comma sign at the thousand point:


  • TaliRXM
    TaliRXM ✭✭✭

    Hi again,

    I did a Test on a brand new sheet and report and it worked like you described.

    I then went back to my original sheet and re-tried including creating a new column and a new report and SUM - but it doesn't work.

    Any advice?

    Thanks!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TaliRXM

    Thanks for letting me know what you tested! I have a few questions for you, based on this information:

    • How many sheets are included in this Report, is it just this one sheet?
    • How are the values in the rows being created (manual input, formula, etc)?
    • How many columns are you using the Summary feature on in the Report?

    Thanks!

    Genevieve

  • TaliRXM
    TaliRXM ✭✭✭

    Hi Genevieve,

    My replies:

    • How many sheets are included in this Report, is it just this one sheet?

    TN: 1 sheet

    • How are the values in the rows being created (manual input, formula, etc)?

    TN: The values are 'Manual input'

    • How many columns are you using the Summary feature on in the Report?

    TN: 1 column

    Thanks!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TaliRXM

    Thank you for your responses! If the Report is only looking at this one sheet and you are Summarizing only this one column, and you have confirmed that the formatting is applied to the entire column (every single cell), then it's unexpected behaviour that the Report won't also reflect this same formatting.

    Since it's a manual input, is there any possibility that someone has entered text anywhere in this column?

    To test, could you try adding a new column with a formula like this:

    =VALUE([Column with numbers]@row)

    Then apply it as a column formula so it runs for every row. Then apply the Column Formatting of $ and .00, and see if you can add this new column to the Report with the correct Summary.

  • TaliRXM
    TaliRXM ✭✭✭

    @Genevieve P. followed your suggestion and it still doesn't work.... SUM shows without $ sign and comma at the thousands point

    Thanks!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TaliRXM

    At this point it's difficult to see what may be happening without having visuals of your sheet and set-up, etc.

    I would suggest reaching out to Smartsheet Support with a screen recording, showing how your column is set up and formatted, how the data is input, that the Summary works on a different sheet with formatting applied, and provide them with the sheet and form URL so they can try to replicate. They'll be able to work with you in a private channel so you can share this information.

    Thank you!

    Genevieve

  • TaliRXM
    TaliRXM ✭✭✭

    And to your question: checked multiple times and there is not text anywhere in this column

  • TaliRXM
    TaliRXM ✭✭✭

    OK, will do

    Thanks Genevieve! Have a great rest of your day (or evening.)

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/19/21

    @TaliRXM

    I've thought of an alternate way to get this information in the meantime! Are you just looking to SUM the value of the entire column, or do you have conditions/filters in the Report?

    If you're only using one sheet, I would set up formula to create your calculation.

    =SUM([Column Name]:[Column Name])

    If you're filtering by anything, you can use a SUMIF function:

    =SUMIF([Criteria Column]:[Criteria Column], "Criteria", [Sum Column Name]:[Sum Column Name])

    You can put this formula in a Sheet Summary field and then format the field to show dollar signs and commas. Then your Dashboard can reference this number as a Metric Widget.

    Let me know if this will work for you, for now!

    Cheers,

    Genevieve

  • TaliRXM
    TaliRXM ✭✭✭

    @Genevieve P. You are awesome! It worked!!!

    Thank you.

    The only issue I had is understanding how to do the formula with filtering. In the report it is easy, but here I had challenges, and it didn't work for me (when was trying filtering out a few cells from the total SUM).

    I will wait for the Smartsheet support team to get back to me and hopefully they will find a solution to the initial issue.


    Thanks again!