Count unique values based on condition

**pradeev** · 06-12-2012, 08:23 AM

Hi All,

I have attached a data sheet which has data from column A to C. Column F to L is the format in which results need to captured.
The data sheet contains running data of open tickets which is captured everyday.
My need is find out unique number of tickets handled by a group on a specific day.
The ticket might be with group1 on June 1st, with group2 on June 2nd and back to group1 on 3rd. in this way there are thousands of tickets and hundreds of groups.
lets assume we have only one ticket in total, so result need to 1 for group1 for 1st june and 1 for group2 for 2nd june and 0 for group1 & group2 on june 3rd.
I request your assitance in finding a solution for this.

**NBVC** · 06-12-2012, 09:28 AM

Try this:

In D2 add formula:

=IF(OR(COUNTIFS(B$1:B2,B2,C$1:C2,C2)>1,COUNTIFS($B$2:$B$52,B2,$C$2:$C$52,"<"&C2)),"","New")

copied down

then in H4:

=COUNTIFS($A$2:$A$52,G$2,$C$2:$C$52,$F4,$D$2:$D$52,H$3)

copied down

copy H4:H21 and paste to J4, then L4.

**MarvinP** · 06-12-2012, 09:50 AM

Hi Pradeev,
I'm not sure I understand the question, but see if this attached helps. I created a helper column to count the number of tags above that are the same and then a Pivot Table similar to what I think you want.

**pradeev** · 06-12-2012, 10:21 AM

Hi MarvinP,

Appreciate your time.
This seems to be good, but need some more help.
Please refer to ticket number "456".
Date Ticket Number Group
20120601 456 Group2
20120602 456 Group2
20120603 456 Group1

Now the table shows this as a unique ticket for Group2 on 20120601, but i want the table to show this as unique ticket for Group1 on 20120603 also.
Please let me know if you need more clarity.

reason for this need : Assume a ticket was with service desk first and was then transfered to 2nd line,(service desk will only transfer tickets and will not troubleshoot them). The current list will show this was handled by service desk only and not by 2nd line, so we need to get results of unique values for each group.

**pradeev** · 06-12-2012, 10:43 AM

Hi NBVC,

Thanks for your effort and time.
I applied the formulas and attached the file with this message.(NBVC.xls)
Please refer to D3, Ticket number "456" first appeared on group 2 and that is not showing up as New, instead it is showing up as new on D35.
456 has 3 intances in the data. two times against group2 and one time against group1. I want the result as 1 for group2 on date 20120601 and 1 for group1 on 20120603.
I have also provided a detailed need in the response to MarvinP's message.
Just some more tweaks can get the results i needed, please do help.