Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

If you already have a Pivot Table set up and all you need is the Misc group totals, then the easiest thing to do is to group everything except your 22 individual groups. To do this, select all those groups, right click then choose group. This will create a new entry in your Row Labels. You can then expand or collapse this to give your ticket counts by individual or grouped groups.

ExcelPivotGroup

If your reporting groups change constantly, it may be worth defining another table and getting more complex, but if they're fairly static, this works well. You can also group them multiple times (e.g. region, manager, business area) to get additional insight into why you're getting so many errors from them.

share|improve this answer
add comment

Just an idea:

Lets say the Group ID's that count are 77, 88, and 99.

At Cell F1 - Ticket Sum for 77, 88 and 99

At F2, or the first Cell you have with it do something like:

=if(D2=88;D2;if(D2=77;D2;if(D2=99;D2;0)))

Just add a =sum(F2:F(endline)) to sum them all up.

share|improve this answer
add comment

Try this

Put a list of Group ID # you want to count somewhere in your workbook. Let say on sheet Groups, column A

Place this formula in cell F2 and copy down for all data rows

=MATCH(D2,Groups!A:A,0)

Use this formula to count misc group IDs

=COUNT(D:D)-COUNTIFS(F:F,"<>#N/A",D:D,">0")
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.