# Calulating tickets for each date.

#### fathima

##### New Member
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
26.3 KB · Views: 10

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### AhoyNC

##### Well-known Member
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
Thankyou. It worked

#### fathima

##### New Member
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

##### Well-known Member

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

Thankyou

#### Attachments

• prod.png
5.7 KB · Views: 5

#### AhoyNC

##### Well-known Member
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<>"")))),"")

Replies
37
Views
2K
Replies
1
Views
36
Replies
11
Views
234
Replies
1
Views
86
Replies
2
Views
189

1,127,334
Messages
5,624,084
Members
416,010
Latest member
NJT

### 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.

### Which adblocker are you using?

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

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