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

 

[fathima]

Date	WEB	CHATS	CALLS	SWOT
3-Jan	INC123
3-Jan	INC123
2-Jan	INC123
2-Jan	ritm52
2-Jan		INC123
3-Jan			INC123
3-Jan				INC123

The above solution was for comparing with only one column, now here i want value as 4
Basically , it has to consider value as 1 if same ticket is repeated in other columns too for specific date

 

[Peter_SSs]

Thankyou so much Peter

You're welcome.

 

[Peter_SSs]

now here i want value as 4

Please explain step by step how you get 4 as I can only see 3 using similar logic as before. The 3 that I can see are:

3-Jan, INC123
2-Jan, INC123
2-Jan, ritm52


Which rows get counted and why?
Which rows do not get counted and why?
Use the row numbers below in your explanations.

fathima lookup.xlsm
	A	B	C	D	E
1	Date	WEB	CHATS	CALLS	SWOT
2	3-Jan	INC123
3	3-Jan	INC123
4	2-Jan	INC123
5	2-Jan	ritm52
6	2-Jan		INC123
7	3-Jan			INC123
8	3-Jan				INC123
Sheet4

 

[fathima]

Sorry, my bad.
Yes the value should be 3.
Any idea how to achieve this?

 

[fathima]

Date	WEB	CHATS	CALLS	SWOT	CTS	BACKLOG
2-Jan	ritm52
2-Jan	ritm52
2-Jan			RITM52
2-Jan			RITM52
2-Jan		RITM52

I will explain you the whole picture how it works and what exactly i need:
Please ignore previous question:

In above excel sheet , chats and calls are somethings which needs to be added even if it falls multiple times on each day. but if same ticket is mentioned in other columns (web, swot,cts,backlog) it should include ticket only once for each day.
Ex: As per example mentioned above, the sum of chats (1) + calls (2) should be 3(though its the same ticket and falls on same day) the sum should be 3 but if the same ticket (ritm52) is updated under web too, its should ignore the ticket under web and should still count as 3

>> chats and calls to be added irrespective of how many times its being repeated whereas if same ticket is mentioned in all other columns (web, swot, cts , backlog) it should count as one




Appreciate your help in advance

 

[fathima]

Date	WEB	CHATS	CALLS	SWOT	CTS	BACKLOG
2-Jan	ritm52
2-Jan	ritm52
2-Jan			RITM52
2-Jan			RITM52
2-Jan		RITM52

2nd senario:
Similar logic but just want to consider tickets starting with 'ritm'
>>chats and calls are something which needs to be added even if it falls multiple times on each day. but if same ticket starting with (ritm) is mentioned in other columns (web, swot,cts,backlog) it should include ticket only once for each day.


Appreciate your help in advance in solving this too

 

[Peter_SSs]

Sorry, my bad.
Yes the value should be 3.

Try this

fathima lookup.xlsm
	A	B	C	D	E
1	Date	WEB	CHATS	CALLS	SWOT
2	3-Jan	INC123
3	3-Jan	INC123
4	2-Jan	INC123
5	2-Jan	ritm52
6	2-Jan		INC123
7	3-Jan			INC123
8	3-Jan				INC123
9
10
11					3
Sheet4

Cell Formulas
Range	Formula
E11	E11	=COUNTA(UNIQUE(A2:A8&B2:B8&C2:C8&D2:D8&E2:E8))

Again I do not understand your last two post requirements. As per previous requests, please specify the following

Which rows get counted and why?
Which rows do not get counted and why?

 

[fathima]

Which rows get counted and why?
Chats and calls column should get counted everytime we enter ticket number
eg:Ex: As per example mentioned below, the sum of chats (1) + calls (2) should be 3(though its the same ticket and falls on same day) the sum should be 3 but if the same ticket (ritm52) is updated under web too, its should ignore the ticket under web and should still count as 3

Date	WEB	CHATS	CALLS	SWOT	CTS	BACKLOG
5-Feb	inc12
5-Feb	inc12
5-Feb		inc12	inc12
5-Feb			inc12
5-Feb				inc12
5-Feb					inc12
5-Feb						inc12
6 feb	inc12
sum	0(5 feb) +1(6 feb)	1	2	1	1	1

If column web, swot, cts, backlog has one or more same tickets matching with chats or calls then it should not count tickets under web, swot, cts and backlog

 

[Peter_SSs]

Which rows get counted and why?
Which rows do not get counted and why?

What I was asking was not for a general description but something more like

Row 1 is included/excluded in the count because ....
Row 2 is included/excluded in the count because ....
Row 3 is included/excluded in the count because ....
Row 4 is included/excluded in the count because ....
.
.
.


Also, you keep referring to "ticket number" but there is no label in your sample data that says "ticket number" and you have given no description of what you mean by "ticket number". Hence we actually have no idea what that means.

 

[Lebene]

My original comment, I have seen your re-explanation:

Spoiler: My original comment

Hello,

You could use:

	A	B	C	D	E
1	Date	WEB	CHATS	CALLS	SWOT
2	3-Jan	INC123
3	3-Jan	INC123
4	2-Jan	INC123
5	2-Jan	ritm52
6	2-Jan		INC123
7	3-Jan			INC123
8	3-Jan				INC123

Calendar/tracker:

	G	H
1		Incident tracker
2	Date	[ENTER DATE]
3	Incident ref	[CREATE DROP-DOWN MENU OF DIFFERENT TICKET NUMBERS]
4	Incident cases	=IF(SUMPRODUCT(($B$2:$E$8=$H1)*($A$2:$A$8>=H2)*(A2:A8>=H2)),1,0)

In my picture you can see I added another RITM52 just to make sure it works. If you remove the IF segment, it will count how many times it appears on that date: =SUMPRODUCT(($B$2:$E$8=$H1)*($A$2:$A$8>=H2)*(A2:A8>=H2))

If you want it to show a date range, then you need to add another row, so "date start" and "date end" - then in the formula change the second part to the "date end" cell: =SUMPRODUCT(($B$2:$E$8=$H1)*($A$2:$A$8>=H2)*(A2:A8>=H3))

edit: just to note that I learned the sumproduct formula to answer your question and then experimented by adding the IF part to it and it happened to work. If anyone more experienced can advise!!

Where I learned the sumproduct formula: Count Items in a Date Range in Excel - Contextures Blog (in the comment section)

Your re-explanation:

Spoiler: Your re-explanation

Date	WEB	CHATS	CALLS	SWOT	CTS	BACKLOG
2-Jan	ritm52
2-Jan	ritm52
2-Jan			RITM52
2-Jan			RITM52
2-Jan		RITM52

1st senario:
I will explain you the whole picture how it works and what exactly i need:
Please ignore previous question:

In above excel sheet , chats and calls are somethings which needs to be added even if it falls multiple times on each day. but if same ticket is mentioned in other columns (web, swot,cts,backlog) it should include ticket only once for each day.
Ex: As per example mentioned above, the sum of chats (1) + calls (2) should be 3(though its the same ticket and falls on same day) the sum should be 3 but if the same ticket (ritm52) is updated under web too, its should ignore the ticket under web and should still count as 3

>> chats and calls to be added irrespective of how many times its being repeated whereas if same ticket is mentioned in all other columns (web, swot, cts , backlog) it should count as one


*****************************
2nd senario:
Similar logic but just want to consider tickets starting with 'ritm'
>>chats and calls are something which needs to be added even if it falls multiple times on each day. but if same ticket starting with (ritm) is mentioned in other columns (web, swot,cts,backlog) it should include ticket only once for each day.

My new formula proposal:
=IF(SUMPRODUCT(($C$2:$E$8=$I$3)*($A$2:$A$8>=$I$2)*($A$2:$A$8<=$I$2)),SUMPRODUCT(($C$2:$E$8=$I$3)*($A$2:$A$8>=$I$2)*($A$2:$A$8<=$I$2)),IFS(SUMPRODUCT(($B$2:$B$8=$I$3)*($A$2:$A$8>=$I$2)*($A$2:$A$8<=$I$2)),1))

1. As you can see in example one, all the ticket instances for CHAT to SWOT are counted (3) but the one INC123 ticket in WEB for the same date is not counted.
2. Now in example two, you can see I deleted the inc from web to swot and so it only counts the one INC123 ticket in WEB.
3. Now in example 3 I have added more than 1 RITM52 ticket in WEB with none in CHAT to SWOT. It shows as only 1 and doesn't count more than one ticket.

I hope this helps with your query.

edit: I realise I mixed up which columns you want to be included in the count. I think this can be changed but I don't have time at the moment. Perhaps you can try yourself with the formula I've provided.

Or anyone else can help?