I'm responsible for creating the monthly metrics for the Helpdesk team that I'm on. I'm using Excel 2007. One report that I'm responsible for creating deals with tickets that we had to return to the group that sent the ticket to my group, because that ticket was assigned to us incorrectly. So in creating this particular report each month, I need to total the number of these returned tickets, for each group that incorrectly assigned the ticket to my group. Each group has its own id number. While there are lots of groups, I'm only interested in the tickets that are returned to particular groups. I have to take these numbers and add them to a "master" report document, for my team to review each month.
Right now I have 22 group id's that I need to calculate totals for. Any groups that fall outside of these 22 are added to a miscellaneous group total. What I normally do is sort the tickets by the column that has the group id. I then change the font color for the 22 group id's that I need counts for. Then I manually count the totals number of tickets for each group id, and add that total to the "master" report document. Then I count the remaining tickets, for which I didn't change the font color, and add that total to the "miscellaneous" group total.
Here's an example of the format of the data that I receive:
Row one contains column headers:
Cell A1 - Date
Cell B1 - Ticket #
Cell C1 - Reason Ticket Returned
Cell D1 - Group ID #
Cell E1 - Group Name
I tried to use a pivot table. That worked to a certain extent. It allowed me to get a total of tickets for all of the group id's. But I'm new to pivot tables, and so I don't know how to use it to allow me to total the tickets for the group id's that need to be in the miscellaneous total.
Thanks in advance for whatever help you can provide.