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:
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.
=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
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.
Thank you. Disappointing.
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
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!
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:
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.
Thanks for letting me know what you tested! I have a few questions for you, based on this information:
TN: 1 sheet
TN: The values are 'Manual input'
TN: 1 column
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.
@Genevieve P. followed your suggestion and it still doesn't work.... SUM shows without $ sign and comma at the thousands point
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.
And to your question: checked multiple times and there is not text anywhere in this column
OK, will do
Thanks Genevieve! Have a great rest of your day (or evening.)
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!
@Genevieve P. You are awesome! It worked!!!
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.
After a form is shared with someone else, is it possible that they add a value in another cell?
Hello, I have notice anytime I try to reference a column in a formula it doesn't automatically recognizes the name and then I always get the unparseable error. For example in below I want Smartsheet to calculate all the item listed as test under the column implementation Manger. =COUNT(Implementaiton Manager:Implementation…
I get data loaded that has a text with it. I want to write a formula to pull the date out. Trying to use this formula, but getting #UNPARSEABLE =VALUE (LEFT ([Daily Rain Total]@row, FIND (" ", [Daily Rain Total]@row)-1))
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2023. All Rights Reserved Smartsheet Inc.