If same ticket is repeated multiple times on single day it should count as one but if same tickets is worked on different date then it should count as

[fathima]

I would like to count values based on dates with 2 criteria's.
I need the count of unique tickets worked by agent on single day for whole month
(If same ticket is repeated multiple times on single day it should count as one but if same tickets is worked on different date then it should count as 2 depending on previous value(Previous value+1).
Please help.
The formula used here is counting unique tickets for whole month
Formula used: 2 attempts made but same result
1st attempt : =SUM(IF((H6=$H$6:$H$27)*($E$6:$E$27<=DATE(2020, 1, 31)), 1/COUNTIFS($H$6:$H$27, H6, $F$6:$F$27, $F$6:$F$27, $E$6:$E$27, "<="&DATE(2020, 1, 31)), ""))
2nd attempt: =SUM(--(FREQUENCY(IF(H6=$H$6:$H$25, COUNTIF($F$6:$F$25, "<"&$F$6:$F$25), ""), (COUNTIF($F$6:$F$25, "0))
(wrong value)
Date TICKETS DISPATCHED ASSIGNEE Total ticket Correct value(to be)
2020-01-24 INC123 Pretty Deena Mathew 2 3
2020-01-24 INC123 Pretty Deena Mathew 2 3
2020-01-25 INC123 Pretty Deena Mathew 2 3
2020-01-24 inc1234 Pretty Deena Mathew 2 3
2020-01-25 ritm012 Rachit Sharma 1 1
Here, first 2 rows value should count 2 as same ticket is worked on single day by one agent,
3rd row should be counting 3 as same ticket is worked on NEXT DAY..

This is very important to resolve.
Please assist
Thanks in advance

 

[jasonb75]

Is this what you need?

Book1
	E	F	G	H	I
5	Date	TICKETS DISPATCHED		ASSIGNEE	Total ticket
6	24/01/2020	INC123		Pretty Deena Mathew	3
7	24/01/2020	INC123		Pretty Deena Mathew	3
8	25/01/2020	INC123		Pretty Deena Mathew	3
9	24/01/2020	inc1234		Pretty Deena Mathew	3
10	25/01/2020	ritm012		Rachit Sharma	1
Sheet2

Cell Formulas
Range	Formula
I6:I10	I6	{=SUM(IFERROR(1/COUNTIFS($E$6:$E$25,$E$6:$E$25,$F$6:$F$25,$F$6:$F$25,$H$6:$H$25,H6),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[fathima]

Hi Jason, Appreciate your help !!!!
Its perfectly correct in your sheet but when i pull this formula and assign same ticket to different agent , values are changing for all agents under count. This should not be happening.

Date	TICKETS DISPATCHED	ASSIGNEE	count
2020-01-24	inc123	Pretty Deena Mathew	4
2020-01-24	inc123	Pretty Deena Mathew	4
2020-01-25	inc123	Pretty Deena Mathew	4
2020-01-26	inc123	Pretty Deena Mathew	4
2020-01-26	inc123	Fathima Balquees	3
2020-01-26	inc000	Fathima Balquees	3

Here, actual count for pretty should be 3 and for fathima should be 2 but when i pull the same formula i am get above values under count.
Please check and assist

Thankyou

 

[Peter_SSs]

Welcome to the MrExcel board!

I see that you have Office 365 so you could have the new UNIQUE and FILTER functions. If so, here is another option to try.

A_MrExcel.xlsm
	E	F	G	H	I
5	Date	TICKETS DISPATCHED		ASSIGNEE	Total ticket
6	24/01/2020	INC123		Pretty Deena Mathew	3
7	24/01/2020	INC123		Pretty Deena Mathew	3
8	25/01/2020	INC123		Pretty Deena Mathew	3
9	24/01/2020	inc1234		Pretty Deena Mathew	3
10	25/01/2020	ritm012		Rachit Sharma	1
Count

Cell Formulas
Range	Formula
I6:I10	I6	=COUNTA(UNIQUE(FILTER(E$6:E$10&"|"&F$6:F$10,H$6:H$10=H6)))

 

[fathima]

Wow, its perfectly working but i see the default value is 1 when pulling formula , Is there any way to set value to 0 by default

 

[Peter_SSs]

Is this what you mean?
=IF(H6="","",COUNTA(UNIQUE(FILTER(E$6:E$10&"|"&F$6:F$10,H$6:H$10=H6,""))))
or this
=IF(H6="",0,COUNTA(UNIQUE(FILTER(E$6:E$10&"|"&F$6:F$10,H$6:H$10=H6,0))))

 

[fathima]

This one!!! =IF(H6="",0,COUNTA(UNIQUE(FILTER(E$6:E$10&"|"&F$6:F$10,H$6:H$10=H6,0)))) -
Thankyou so much Peter!
You have being of great help. Really appreciate your talent

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.

 

[fathima]

hi, If i need to show agents who have worked on total tickets so far in another sheet, what would be the formula.
Here is the example for 2 agents

Pretty Deena Mathew	3
Pretty Deena Mathew	3
Fathima Balquees	2
Pretty Deena Mathew	3
Pretty Deena Mathew	3
Fathima Balquees	2
Fathima Balquees	2

here, single agent has same value.
I am trying to show this in another sheet for about 60 agents

EG:

Pretty Deena Mathew	3
Fathima Balquees	2

 

[Peter_SSs]

Is this what you are looking for?

fathima lookup.xlsm
	A	B
1
2	Pretty Deena Mathew	3
3	Pretty Deena Mathew	3
4	Fathima Balquees	2
5	Pretty Deena Mathew	3
6	Pretty Deena Mathew	3
7	Fathima Balquees	2
8	Fathima Balquees	2
Sheet1

fathima lookup.xlsm
	A	B
1
2	Pretty Deena Mathew	3
3	Fathima Balquees	2
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=VLOOKUP(A2,Sheet1!A$2:B$100,2,0)