Calulating tickets for each date.

fathima

New Member
Joined
Jan 29, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • excel.png
    excel.png
    26.3 KB · Views: 10

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,746
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
1
2SLNODateTicketAssignee5/1/20205/2/2020
35/1/2020inc12FathimaFathima11
45/1/2020inc12FathimaAmeena 1
55/2/2020inc12Fathima
65/2/2020inc13Ameena
Sheet1
Cell Formulas
RangeFormula
I3:J4I3=IFERROR(ROWS(UNIQUE(FILTER($B$3:$C$6,($E$3:$E$6=$H3)*($B$3:$B$6=I$2)))),"")
 

fathima

New Member
Joined
Jan 29, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
A small question,
SL NODateWEB ticketsum
1​
1-May​
inc71 (since 1 web is mentioned for 1 date ie MAY 1st)
2​
1-May​
inc70 (since same ticket worked on one same date)- May 1st
3​
1-May​
inc31 (since different ticket worked on same day) May 2nd
4​
2-May​
inc71 ( Should add since same ticket worked on different day) May 2nd
total3
I Just need value for total mentioned in last row ie (sum of all tickets worked on logic mentioned separately in each row)
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,746
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try:
Book1
ABCD
1SL NODateWEB ticketsum
211-Mayinc7
321-Mayinc7
431-Mayinc3
542-Mayinc7
6total3
Sheet1
Cell Formulas
RangeFormula
D6D6=ROWS(UNIQUE(B2:C5))
 

fathima

New Member
Joined
Jan 29, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • prod.png
    prod.png
    5.7 KB · Views: 5

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,746
Office Version
  1. 365
Platform
  1. Windows
See if this works for you:

Book1
ABCDEFGHI
1
2Total3Total 
3SL NODateWEB ticketsumSL NODateWEB ticketsum
415/1/2020inc75/1/2020
525/1/2020inc7Inc5
65Inc5
765/1/2020
87
935/1/2020inc3
1045/2/2020inc7
11
12Total 
13SL NODateWEB ticketsum
14
15
16
Sheet1
Cell Formulas
RangeFormula
D2D2=IFERROR(ROWS(UNIQUE(FILTER(B4:C10,($B$4:$B$10<>"")*($C$4:$C$10<>"")))),"")
I2I2=IFERROR(ROWS(UNIQUE(FILTER(G4:H10,($G$4:$H$10<>"")*($G$4:$H$10<>"")))),"")
D12D12=IFERROR(ROWS(UNIQUE(FILTER(B14:C16,($B$14:$B$16<>"")*($C$14:$C$16<>"")))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,717
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top