Count issues by owner where status does not contain the word closed

Options

I have a UAT issues tracker with a drop down of various statuses, Assigned to for the owner of the issue, Assigned date which reflect the date the issue was assigned. I am trying to setup a dashboard of various charts reflecting UAT issues summaries. I've created summary formulas to count the number of issues by status. I now want to setup a few other charts.

  1. Total non closed issued by assigned date - I have setup a separate sheet with a column for Date Assigned that shows each date from 10/3/22 to 11/18/22. I've setup this formula but can't figure how to exclude the 4 types of closed issues. Each type contains the word "Closed" followed by Design Change, Non-Issue, Duplicate, etc.
  2. Total non closed issues by Assigned to - I can't figure out how to get this to work. I tried to create a separate sheet with a column of Assigned To Names listed out separately but can't get the count to work.


Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    @A.Vo you can use the Filter button on the Report to exclude Status closed...

    While you see the list in the report, once you point your dashboard chart to the report, you will only see the dates and the counts.

    Does that help?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    Hi @A.Vo

    For your #1, do you want to show a number for each date? If each of your issues live on a separate row, you can use a report to show this. Point your report at the tracker sheet, bring in the date column, then use the filters to filter out the values you do not want to see. Use the Group By button to group by your date. Then use the Summarize button to count by the date. Let me know if that gives you what you're looking for.

    For your #2 you can create a similar report as the above for this one. Filter out the closed issues. Group by the Assigned To and Summarize by count of the assigned to.

    Does that help?

    -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • A.Vo
    Options

    Ryan, I want to ultimately depict the data in a chart so just want a count of # of open issues by date and count of # of open issues by Assigned to person.

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @A.Vo you can do that with a report, then point your chart to that report.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • A.Vo
    Options

    Ryan, yes I planned on creating a report but I need a count of the issues not list of issues. It the count formula to exclude status that contains closed that I can't figure out.

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    @A.Vo you can use the Filter button on the Report to exclude Status closed...

    While you see the list in the report, once you point your dashboard chart to the report, you will only see the dates and the counts.

    Does that help?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • A.Vo
    Options

    Thanks Ryan...seems I was trying to make it more complicated than necessary. :-)

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @A.Vo my pleasure! We all fall into that boat some times. :) -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!