Smartsheet Basics

Smartsheet Basics

Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.

Hello how can I produce percentage through the reports Data that are not percentage

✭✭
edited 02/19/25 in Smartsheet Basics

for example -

I have 5 employees who fill in online forms and I have to produce a report from all forms that will be divided into a percentage according to each employee

Michal - filled 5 forms

yoni - filled 5 forms

total of 10 forms which means that each one of them had 50% work.

i want to show it on the dashboard as a pai or Donut

(google sheets does it)

Best Answers

  • Employee
    Answer ✓

    Hi @Gaia Amaia!

    A different approach from the one provided by @Adam Costello is to calculate the percentage directly within the Sheet Summary before visualizing it in a report or dashboard. Here's how you can do it:

    1. Set up a sheet to collect form submissions and include a Created By column to track who submitted each entry. You can enable this in the form settings by requiring Smartsheet login.
    2. Use a formula in the Sheet Summary to calculate the percentage of submissions per user:

    =COUNTIF([Created by]:[Created by], "user's email") / COUNTIF([Created by]:[Created by], <>""")

    • COUNTIF([Created by]:[Created by], "user's email")
      • counts the number of forms submitted by a specific user.
    • COUNTIF([Created by]:[Created by], <>""")
      • counts the total number of submissions.

    3. Create a Sheet Summary Report using the sheet as your source. This report will compile the summary data for each user.

    4. Build a Dashboard and use a Chart Widget (Pie or Donut) with the report as the data source to display the percentage breakdown.

    For more details on COUNTIF, see this article: COUNTIF Function.

    Hope this helps!

    Cheers,
    Isaac.

    Need more information? 👀 |Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 |Global Discussions

  • Employee
    Answer ✓

    Hi @Gaia Amaia!

    If you're the only one entering the information but still have tasks corresponding to different team members, using the "Assigned To" column would help. This way, you can calculate the percentages without a problem.

    For example, if you want to calculate the percentage of tasks assigned to a specific team member, you can use the following formula:

    =COUNTIF("Assigned To":"Assigned To", "Team Member Name") / COUNT("Assigned To":"Assigned To")

    This counts the number of tasks assigned to a specific person and divides it by the total number of tasks in the sheet. If needed, multiply by 100 or apply the Percentage Format in Smartsheet:

    =(COUNTIF("Assigned To":"Assigned To", "Team Member Name") / COUNT("Assigned To":"Assigned To")) * 100

    Regarding the percentage values you're seeing, remember that when working with percentages, you need to use decimal values. The Percentage Format in Smartsheet formats numbers as percentages. If you format a cell this way, a value like 0.95 is displayed as 95%, while a value of 95 is shown as 9,500%. You can find more details here: Format Your Data.

    I hope this helps! Let me know if you have any questions.

    Cheers,
    Isaac.

    Need more information? 👀 |Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 |Global Discussions

Answers

  • ✭✭✭✭

    Hi Gaia,

    First, you need to set up your form to record who submitted it. You can either do this through form settings by selecting "Require Smartsheet login to access this form" and then adding the "created by" column type. Or by having them enter their names manually.

    Then you have to make a report that pulls information from your original sheet. When you make the column selection make sure you include the column that has the submitters names or created by. Next, in the report, select the group option and group your name column. Then, us the "Summary" option to summarize your names and "Count".

    Once this is done, you have to created a dashboard. Add a graph widget and select your report. You can have a pie or donut chart. But it should fill in the info automatically.

    Hope this helps!

  • Employee
    Answer ✓

    Hi @Gaia Amaia!

    A different approach from the one provided by @Adam Costello is to calculate the percentage directly within the Sheet Summary before visualizing it in a report or dashboard. Here's how you can do it:

    1. Set up a sheet to collect form submissions and include a Created By column to track who submitted each entry. You can enable this in the form settings by requiring Smartsheet login.
    2. Use a formula in the Sheet Summary to calculate the percentage of submissions per user:

    =COUNTIF([Created by]:[Created by], "user's email") / COUNTIF([Created by]:[Created by], <>""")

    • COUNTIF([Created by]:[Created by], "user's email")
      • counts the number of forms submitted by a specific user.
    • COUNTIF([Created by]:[Created by], <>""")
      • counts the total number of submissions.

    3. Create a Sheet Summary Report using the sheet as your source. This report will compile the summary data for each user.

    4. Build a Dashboard and use a Chart Widget (Pie or Donut) with the report as the data source to display the percentage breakdown.

    For more details on COUNTIF, see this article: COUNTIF Function.

    Hope this helps!

    Cheers,
    Isaac.

    Need more information? 👀 |Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 |Global Discussions

  • @Isaac A.

    thank you, can you help me with a formula for the same issue but without created by?

    I have another table that I'm the only one that create lines but its for my team and i want to do the same thing there.

  • why the % is not until 100%?

  • Employee
    Answer ✓

    Hi @Gaia Amaia!

    If you're the only one entering the information but still have tasks corresponding to different team members, using the "Assigned To" column would help. This way, you can calculate the percentages without a problem.

    For example, if you want to calculate the percentage of tasks assigned to a specific team member, you can use the following formula:

    =COUNTIF("Assigned To":"Assigned To", "Team Member Name") / COUNT("Assigned To":"Assigned To")

    This counts the number of tasks assigned to a specific person and divides it by the total number of tasks in the sheet. If needed, multiply by 100 or apply the Percentage Format in Smartsheet:

    =(COUNTIF("Assigned To":"Assigned To", "Team Member Name") / COUNT("Assigned To":"Assigned To")) * 100

    Regarding the percentage values you're seeing, remember that when working with percentages, you need to use decimal values. The Percentage Format in Smartsheet formats numbers as percentages. If you format a cell this way, a value like 0.95 is displayed as 95%, while a value of 95 is shown as 9,500%. You can find more details here: Format Your Data.

    I hope this helps! Let me know if you have any questions.

    Cheers,
    Isaac.

    Need more information? 👀 |Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 |Global Discussions

Trending in Smartsheet Basics