0

I would like to count unique emails that every employee received per interval. I'm using FREQUENCY and MATCH but I'm getting incorrect outputs. Please see the sample table below. Employee A gets 1 email at 08:00, 1 new email at 09:00 and so on. Thanks!

sample

  • 1
    If you have the Office 365: =COUNT(UNIQUE(FILTER(A:A,C:C="A")) – Scott Craner Feb 12 at 23:53
  • unfortunately, we still use 2013 :( thanks though! – Mrcvz Feb 13 at 14:23
  • What is the logic of deciding whether an email received at a certain hour is unique? – Ron Rosenfeld Feb 13 at 17:14
  • i am basing it on the subject line, for example on the 9am interval, out of the 3 emails that employee A - there are 2 different emails ACH and ACHH – Mrcvz Feb 14 at 0:05
  • @Mrcvz Why are you not counting email Follow Up? – Ron Rosenfeld Feb 15 at 23:30
0

You can use a Pivot Table.

  • In the Pivot Table Create dialog, select the option to Add this data to Data Model
    • If you do that, Distinct Count will appear as an option under the Value Field settings.
  • Drag Time and Employee to the Rows area
  • Drag Emails to the Values area, and select Distinct Count in the Value Field Settings.
  • Format to taste.
    • I chose Show in Tabular Form and Repeat all Item Labels

Original Data

enter image description here

Pivot with Rows Order: Time | Employee

enter image description here

Pivot with Rows Order: Employee | Time

enter image description here

  • thanks for this! i think this would be the easiest way to do this - i never knew it was this option i need to check – Mrcvz Feb 16 at 18:34
  • @Mrcvz It is certainly not a well documented feature. I'd been using Excel for many years before stumbling across it. – Ron Rosenfeld Feb 16 at 21:09
  • i have a follow up on this, for employee A - he received the ACH emails specifically at the 8 and 9am intervals, how can i disregard counting the one at the 9am interval? reason for this is that it is the same email he got from 8am. if i were to do it manually, employee A received 1 unique email at 8am, 2 at 9am (as the other ACH is actually ACHH for distinction that it is a new email) – Mrcvz Feb 19 at 0:21
  • @Mrcvz How do you know, if an email is logged in at different times, if it is a new email or a duplicate? If different emails to the same person will always have a slight difference in the Email column, then, before creating the Pivot Table, select the Email and Employee columns and use the Remove Duplicates tool. – Ron Rosenfeld Feb 19 at 0:39
  • @Mrcvz That should read "select the entire table, but just check off the email and employee columns" in the Remove Duplicates Dialog. – Ron Rosenfeld Feb 19 at 0:46
0

You can try following formula:

=1/SUMPRODUCT((C2=$C$2:$C$23)*(A2=$A$2:$A$23)*(B2=$B$2:$B$23))

In your sample all emails per hour/employee was unique so I changed one at 9:00/A

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.