Calulating tickets for each date.

[fathima]

As can be seen from image, i have calculated whole month productivity for all agents with respect to unique date and time for each
The logic used is:
if same ticket(incident number) worked by same agent on single day, the productivity for agent will be one even if u enter same ticket for same date multiple times.(logic already impleted)
If same ticket worked by same agent on another day then the productivity will be counted as 1 for next day so total productivity for 2 days will be 2
This is how it is reflecting in F column under count... All data(productivity) is shown for entire month using formula under count "=IF(E6="",0,COUNTA(UNIQUE(FILTER(B$6:B$100000&"|"&C$6:C$100000,E$6:E$100000=E6,0)))) "
but now i need to filter on single day basis for each agent, In the above it shows unique values for whole month for each user . please help. nee to submit this by tomorrow

eg: for fathima under I column it has to show 1 under 1st may again 1 under 2nd may (im getting result for both days as 2 under F for whole month)
likewise for for Ameena it has to show 1 under 2nd may

 

[AhoyNC]

Try:
Copy formula in I3 down and across. This formula assumes that the dates in column B and the ones going across row 2 are Excel dates and not text.
Change ranges to match your data.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	SLNO	Date	Ticket		Assignee				5/1/2020	5/2/2020
3		5/1/2020	inc12		Fathima			Fathima	1	1
4		5/1/2020	inc12		Fathima			Ameena		1
5		5/2/2020	inc12		Fathima
6		5/2/2020	inc13		Ameena
Sheet1

Cell Formulas
Range	Formula
I3:J4	I3	=IFERROR(ROWS(UNIQUE(FILTER($B$3:$C$6,($E$3:$E$6=$H3)*($B$3:$B$6=I$2)))),"")

 

[fathima]

Thankyou. It worked

 

[fathima]

A small question,

SL NO	Date	WEB ticket	sum
1​	1-May​	inc7	1 (since 1 web is mentioned for 1 date ie MAY 1st)
2​	1-May​	inc7	0 (since same ticket worked on one same date)- May 1st
3​	1-May​	inc3	1 (since different ticket worked on same day) May 2nd
4​	2-May​	inc7	1 ( Should add since same ticket worked on different day) May 2nd
		total	3

I Just need value for total mentioned in last row ie (sum of all tickets worked on logic mentioned separately in each row)

 

[AhoyNC]

Try:

Book1
	A	B	C	D
1	SL NO	Date	WEB ticket	sum
2	1	1-May	inc7
3	2	1-May	inc7
4	3	1-May	inc3
5	4	2-May	inc7
6			total	3
Sheet1

Cell Formulas
Range	Formula
D6	D6	=ROWS(UNIQUE(B2:C5))

 

[fathima]

2 filtration needed,

1)The default value shows 1 even when no data is entered as u can see below in the image, It should show 0 by default whereas its showing 1
2) 2nd scenario: It should only consider criteria or sum if both values (date and web tickets ) are entered but in this case if either of data is entered it is counting values along with default value 1
Please refer image

Thankyou

 

[fathima]

Please help someone.. please.....

 

[AhoyNC]

See if this works for you:

Book1
	A	B	C	D	E	F	G	H	I
1
2			Total	3				Total
3	SL NO	Date	WEB ticket	sum		SL NO	Date	WEB ticket	sum
4	1	5/1/2020	inc7				5/1/2020
5	2	5/1/2020	inc7					Inc5
6	5		Inc5
7	6	5/1/2020
8	7
9	3	5/1/2020	inc3
10	4	5/2/2020	inc7
11
12			Total
13	SL NO	Date	WEB ticket	sum
14
15
16
Sheet1

Cell Formulas
Range	Formula
D2	D2	=IFERROR(ROWS(UNIQUE(FILTER(B4:C10,($B$4:$B$10<>"")*($C$4:$C$10<>"")))),"")
I2	I2	=IFERROR(ROWS(UNIQUE(FILTER(G4:H10,($G$4:$H$10<>"")*($G$4:$H$10<>"")))),"")
D12	D12	=IFERROR(ROWS(UNIQUE(FILTER(B14:C16,($B$14:$B$16<>"")*($C$14:$C$16<>"")))),"")