Format SUM function in a report

TaliRXM

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!

Genevieve P.

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

Krissia B.

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

Thank you. Disappointing.

Will do

Tali

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.

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

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

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.

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

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.

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

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

Thanks!

Genevieve P.

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

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

TaliRXM

OK, will do

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

Genevieve P.

@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

@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!