SOLVED

Formula: Count if the ticket is in particular time, date and channel

Brass Contributor

Hello,

 

I need a count formula for E3:E26 in spreadsheet called, 'Ticket Time.'

 

1. In spreadsheet, 'Ticket Time,' I want a formula in E3:E26 which will count how many tickets fall within specific time for 'call' e.g. ticket created in 08:11:59 would be counted in row 11. 

The source of information is in column B and C called 'Created' and 'Time', of spreadsheet '2020 All Tickets'. Please note that the count has to be within the selected dates shown in cell B3:B4 of spreadsheet, 'Ticket Time.'

2 . 'call' tickets can be found in the mix of texts in Column D named 'Tags' in spreadsheet '2020 all tickets'.

 

Ticket TimeTicket Time2020 All Tickets2020 All Tickets

 

 

5 Replies

@little2fern

 

Hi,

 

Please try this formula:

 

=COUNTIFS('2020 All Tickets'!$B$2:$B$16199,">="&$B$3,
'2020 All Tickets'!$B$2:$B$16199,"<="&$B$4,
'2020 All Tickets'!$C$2:$C$16199,">="&K3,
'2020 All Tickets'!$C$2:$C$16199,"<="&L3)

 

image.png

 

To simplify the process, I've created two columns (From/To) next to the table.

 

Hope that helps

@Haytham Amairah 

 

Hello Haytham,

 

Thank you for your reply, however the formula isn't exactly what I am looking for yet.

The formula you shared counts how many tickets are created within specific time and date, but has no ticket channel type added to the formula.Screen Shot 2563-06-12 at 09.22.03.png

I want to count different ticket channel (e.g. call, fb, inapp, email and twitter) and how each appear within specific time and date. 

The formula will show how many tickets that came by 'call' channel at 6.00am between start date x to end date y for example.

You can find the ticket channel type in the mix of text in 2020 All Tickets, column D. 

 

Best regards,

Fern

Hi

 

What could do is, just add an additional column as "Ticket chanel" (call, fb, inapp, email, twitter), and generate a pivot table from this data.

If you need help, can you can contact.

 

Regards

Supreeth

supreeth.guptha@gmail.com
7019131376

@little2fern Added a column E to "2020 All Tickets" that determines the channel from the Tags in D. From there, I created a pivot table in PT that counts all tickets by channel, grouped by hour. Note that there are 356 tickets for which there seems to be no reference to any of the channels you mentioned.

See attached file.

 

Edit: Forgot to add the date filter. Now using the pivot table Filter field where you select the dates to be counted. Perhaps not the most elegant solution, but an easy one.

 

Edit2: Added an alternative solution using an intermediate step to FILTER the tickets for the required week and then created the PT on the filtered data. 

best response confirmed by little2fern (Brass Contributor)
Solution

@little2fern

 

Hi Fern,

 

You can go with the solution suggested by @Riny_van_Eekelen.

But I would expand my suggested formula the handle the additional criteria you mentioned.

 

To get this analysis done, you need to extract all channels you need from Tags columns in a separate column.

You can do that with this formula below and also with the formula suggested by @Riny_van_Eekelen.

=IF(SUMPRODUCT(IFERROR(SEARCH($H$5:$H$9,D2),0)),INDEX($H$5:$H$9,MATCH(TRUE,ISNUMBER(SEARCH($H$5:$H$9,D2)),0)),"")

 

After that, you can expand the COUNTIFS function to take this additional column into account as follows:

=COUNTIFS('2020 All Tickets'!$B$2:$B$16199,">="&$B$3,
'2020 All Tickets'!$B$2:$B$16199,"<="&$B$4,
'2020 All Tickets'!$C$2:$C$16199,">="&$K3,
'2020 All Tickets'!$C$2:$C$16199,"<="&$L3,
'2020 All Tickets'!$E$2:$E$16199,E$2)

image.png

 

Hope that helps

1 best response

Accepted Solutions
best response confirmed by little2fern (Brass Contributor)
Solution

@little2fern

 

Hi Fern,

 

You can go with the solution suggested by @Riny_van_Eekelen.

But I would expand my suggested formula the handle the additional criteria you mentioned.

 

To get this analysis done, you need to extract all channels you need from Tags columns in a separate column.

You can do that with this formula below and also with the formula suggested by @Riny_van_Eekelen.

=IF(SUMPRODUCT(IFERROR(SEARCH($H$5:$H$9,D2),0)),INDEX($H$5:$H$9,MATCH(TRUE,ISNUMBER(SEARCH($H$5:$H$9,D2)),0)),"")

 

After that, you can expand the COUNTIFS function to take this additional column into account as follows:

=COUNTIFS('2020 All Tickets'!$B$2:$B$16199,">="&$B$3,
'2020 All Tickets'!$B$2:$B$16199,"<="&$B$4,
'2020 All Tickets'!$C$2:$C$16199,">="&$K3,
'2020 All Tickets'!$C$2:$C$16199,"<="&$L3,
'2020 All Tickets'!$E$2:$E$16199,E$2)

image.png

 

Hope that helps

View solution in original post