Counting values of one column based on the uniqueness of the values in another column

Hi there,

Here is a table

Col1 Col2 Col3 Col4
88210 15m 14:44.0 E__uMail
92663 21m 34:13.0 E__uMail
92677 165m 23:43.0 E__uMail
93094 15m 07:24.0 E__uMail
93094 15m 42:12.0 E__uMail
93167 10m 41:33.0 E__uMail
93282 2m 12:43.0 E__uMail
93411 12m 48:55.0 Voicemail
93567 5m 35:34.0 E__uMail
93617 15m 56:09.0 E__uMail
93713 15m 27:59.0 E__uMail
93713 45m 52:19.0 E__uMail
93713 100m 14:19.0 E__uMail
93713 60m 43:29.0 E__uMail
93713 5m 50:42.0 E__uMail
93764 15m 40:38.0 Walk__bIn
93820 15m 17:09.0 E__uMail
93906 10m 16:27.0 Ask__bIT
93906 5m 08:36.0 Ask__bIT
93920 15m 47:37.0 E__uMail
93939 10m 32:17.0 E__uMail
93990 15m 58:48.0 E__uMail
94016 6m 52:44.0 E__uMail
94030 8m 01:45.0 Walk__bIn

Here is some context

Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).

In this table the number of tickets that were:
submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1

Col1 Col2 Col3 Col4
88210 15m 14:44.0 E__uMail = 1
92663 21m 34:13.0 E__uMail = 2
92677 165m 23:43.0 E__uMail = 3
93094 15m 07:24.0 E__uMail = 4
93094 15m 42:12.0 E__uMail = 4
93167 10m 41:33.0 E__uMail = 5
93282 2m 12:43.0 E__uMail = 6
93411 12m 48:55.0 Voicemail = 1
93567 5m 35:34.0 E__uMail = 7
93617 15m 56:09.0 E__uMail = 8
93713 15m 27:59.0 E__uMail = 9
93713 45m 52:19.0 E__uMail = 9
93713 100m 14:19.0 E__uMail = 9
93713 60m 43:29.0 E__uMail = 9
93713 5m 50:42.0 E__uMail = 9
93764 15m 40:38.0 Walk__bIn = 1
93820 15m 17:09.0 E__uMail = 10
93906 10m 16:27.0 Ask__bIT = 1
93906 5m 08:36.0 Ask__bIT = 1
93920 15m 47:37.0 E__uMail = 11
93939 10m 32:17.0 E__uMail = 12
93990 15m 58:48.0 E__uMail = 13
94016 6m 52:44.0 E__uMail = 14
94030 8m 01:45.0 Walk__bIn = 2

What I need is a formula that will produce this result.

Thanks,
crayadder

Hi Richard,

Thanks for the link.

Let me clarify about the emails. In the table there 14 unique tickets that were submitted via email - Col1 shows the ticket number. There are indeed 19 rows with email in them, but some of those rows belong to the same ticket and what I am trying to do is remove all the rows of a ticket except 1 and then I can get an accurate count of how many tickets were submitted via which submission type.

Hi Richard,

Thanks! The formula you used to create the helper column is exactly what I am looking for. All I need to do is do a count on each value and I am done!

Greatly appreciated.