Formula to get a percentage of a column and for it then to show up on a dashboard.

C12HD

I am very new to this, so this is a very basic question. I have sales numbers that will be entered on a monthly bases. I need a column that will show the how close they are to their goal amount, which will then show up a graph on a dashboard. These numbers will be coming in by a form filled out by the client. The sheet will have the totals for every month.... but I would like for the %met column and dashboard to update it to the most recent amount entered (the current months sales amount).

SJM1609

Hi @C12HD ,

1) Try this:

note: the writing within the square brackets should be changed to suit your data's column headings.

2) Then select the column and change the format of the column to percentage by selecting the '%' icon.

3) Lastly right click on the cell with the formula in and convert it to a column formula.

Dashboards are dynamic and will reflect the data selected. You can watch some videos in the Smartsheet university to gain more understanding.

Hope this helps.

Nick Korna

Hi @C12HD,

This is absolutely something that can be done, though some of the specifics will depend on how your sales data is entered.

Is it possible to show a screenshot of the sheet with this in it? Hide any columns with sensitive data as what a solution will depend on is largely how the date and totals are presented.

What I would suggest is having another sheet containing columns with Month & Year (or a date to reflect these e.g. the first of the month) and the monthly goal/target. You can then use cross sheet references in a SUMIFS formula to get the total for these months and calculate the percentage - how exactly may depend on if you want to display the data (for example would you want to show the amount or just jump straight to the percentage).

You can then use this second sheet as the basis for a report which then feeds through into your dashboard.

Alternatively you could use sheet summaries in the original sheet, but this would require more manual updating (date ranges, target goal) to keep up to date.