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

 

[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?

@fathima
Hi, I amended the formula above, it should be this because I realise now that you said, if it shows up in WEB at all, it should count that as one: =IF(SUMPRODUCT((B2:B8=$I$3)*($A$2:$A$8>=$I$2)*($A$2:$A$8<=$I$2)),1,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)),0))

I think, for now, this is as far as I can go. The only way I can see it being fixed is if "web, swot,cts,backlog" are next to each other in the spreadsheet and then CALLS and CHATS columns are next to each other in the front or end of this list, then you can just adjust the cell range. I don't know how to make Excel understand to sum the WEB column, then skip CALLS and CHAT, and then continue to check.
So it should be arranged like this:

CHAT	CALLS	WEB	SWOT	CTS	BACKLOG

Also, now I realise that your ticket numbers may be unique to one person so you'd need the formula to only count that unique ticket number? The scenario you drew up didn't have enough context in my opinion...

Anyway, I have uploaded the file and you can experiment and see... WeTransfer link SUMPRODUCT EXPERIMENT share.xlsx

@Peter_SSs Perhaps the re-explanation makes more sense, they posted another thread and re-explained it there.

How I understand it, is that RITM52 and INC123 (I assume is short for 'incident') are ticket numbers.
They have different ways that these tickets are logged?
So when the ticket is logged via a call or chat, they want to count each individual time it appears for a specific date.
But if that ticket number is logged by web, swot, cts or backlog it should only count it once.

I have achieved that with my desperate formula above, but it will only work if the columns are arranged like above with chat and calls together in the front or back. At the moment my formula can't skip cells to search.

 

[fathima]

Hi Lebene,
You have understood my requirement so well. Thankyou very much for your time!! I am creating this for single agent , so we do not have to filter for different people . The whole tracker is for one person to keep record of the tickets worked, I do not mind changing the column arrangement, please help me with formula because i need the solution in one cell

"How I understand it, is that RITM52 and INC123 (I assume is short for 'incident') are ticket numbers.
They have different ways that these tickets are logged?
So when the ticket is logged via a call or chat, they want to count each individual time it appears for a specific date.
But if that ticket number is logged by web, swot, cts or backlog it should only count it once. "

@Peter_SSs This is what i am exactly looking for
@Lebene Thankyou for explaining it to Peter.


To add on to your explanation,
I need the same logic to be applied for incidents starting with 'ritm' and 'inc' separately to keep count of them separately.



@Lebene: I tried modifying formula shared by you , it only shows result when u choose specific date for specific incident manually, but i need the value to be displayed dynamically as the incident is logged with respect to all the dates mentioned in the date column


Appreciate your assistance in advance
Thankyou

 

[fathima]

So basically i need solution for 2 issues:

1st - is to count values for all tickets(including inc and ritm)
2nd - is to count values for tickets starting with 'inc' only
3rd-is to count values for tickets starting with 'ritm' or 'req' only(some tickets starts with req but it is same as ritm so both can be included together )

 

[fathima]

Can anyone help me on this?

 

[sandy666]

like this?

 

[fathima]

TOTAL TICKETS	2		PRODUCTIVITY TRACKER FEBRUARY 2020
INC	RITM		CHATS	CALLS	SWOT	CTS	BACKLOG
1	1		15	8	3	0	0
		TOTAL TICKETS
SL NO	Date	WEB	CHATS	CALLS	SWOT	CTS	BACKLOG
1	1-Feb		INC3486847
2	1-Feb	RITM2427016

Like this

 

[Lebene]

Hi Lebene,
You have understood my requirement so well. Thankyou very much for your time!! I am creating this for single agent , so we do not have to filter for different people . The whole tracker is for one person to keep record of the tickets worked, I do not mind changing the column arrangement, please help me with formula because i need the solution in one cell

"How I understand it, is that RITM52 and INC123 (I assume is short for 'incident') are ticket numbers.
They have different ways that these tickets are logged?
So when the ticket is logged via a call or chat, they want to count each individual time it appears for a specific date.
But if that ticket number is logged by web, swot, cts or backlog it should only count it once. "

@Peter_SSs This is what i am exactly looking for
@Lebene Thankyou for explaining it to Peter.


To add on to your explanation,
I need the same logic to be applied for incidents starting with 'ritm' and 'inc' separately to keep count of them separately.



@Lebene: I tried modifying formula shared by you , it only shows result when u choose specific date for specific incident manually, but i need the value to be displayed dynamically as the incident is logged with respect to all the dates mentioned in the date column


Appreciate your assistance in advance
Thankyou

Hi Fathima,

I'm very sorry but I have really exhausted my knowledge and creativity (however janky it is). But I'll continue to watch your thread as I'm interested in the resolution.

 

[fathima]

Any update ?