What's the best way to get a total count and also categorize the results?

Ashley Klaers

I'm creating a dashboard for our team on submitted tickets but the requestor wants this data to show total ticket amount per team member and also have it broke down into completion dates.

Ex:

Jane Doe:

Total Tickets 100

Completed 1 day or less - 50

Completed in 2 days - 25

Completed in 3 or more - 50

I'm pulling this data from another sheet from 2 separate columns; I'm still somewhat new to formulas and I'm just not sure what my best approach would be. I've been pulling form the "Task For" column for who it's assigned too and the Days to Complete/Column24 for the completion date range.

Ryan Sides

Hi Ashley, if your list of teams members is “known” then you can create a sheet that lists your team members and create 3 columns. One for each date category. Create cross sheet formulas using the countifs function to count how many tickets fall into those categories for each person.

If your list of team members is not know. (I.e every person in your 5000 employee org can submit a ticket) then I would add those 3 columns to the sheet in your screenshot and add formulas there to determine to which category that ticket falls. Each new column could be a checkbox. Then create a report that pulls in the name and the 3 columns. Then sum each column and group by team member.

Do either of those work for you?

Ryan Sides

Hi Ashley, if your list of teams members is “known” then you can create a sheet that lists your team members and create 3 columns. One for each date category. Create cross sheet formulas using the countifs function to count how many tickets fall into those categories for each person.

If your list of team members is not know. (I.e every person in your 5000 employee org can submit a ticket) then I would add those 3 columns to the sheet in your screenshot and add formulas there to determine to which category that ticket falls. Each new column could be a checkbox. Then create a report that pulls in the name and the 3 columns. Then sum each column and group by team member.

Do either of those work for you?

Ashley Klaers

Yes the first option worked and I was able to get the data to read as I needed. Thanks for the advice and ideas Ryan!

Ryan Sides

You're welcome! Glad it worked out.