In an Excel spreadsheet, I have dates in column B and email addresses in column K.
I need the number of unique emails that appear in column K for each date. Sometimes the email addresses appear multiple times.
B1: 7/18/15, K1: email@example.com B2: 7/18/15, K2: firstname.lastname@example.org B3: 7/18/15, K3: email@example.com B4: 7/18/15, K4: firstname.lastname@example.org B5: 7/18/15, K5: email@example.com B7: 7/19/15, K7: firstname.lastname@example.org B8: 7/19/15, K8: email@example.com B9: 7/19/15, K9: firstname.lastname@example.org B10: 7/19/15, K10: email@example.com B11: 7/19/15, K11: firstname.lastname@example.org
Then, in columns P & Q, I want the date and number of unique emails in column K, totaled by each date, on its own blank line.
P6: 7/18/15 Q6: 4 (using this formula: =SUMPRODUCT((K1:K5<>"")/COUNTIF(K1:K5,K1:K5&"")) P12: 7/19/15 Q12: 3 (using this formula: =SUMPRODUCT((K7:K11<>"")/COUNTIF(K7:K11,K7:K11&""))
QUESTION: How to create a formula with conditions, if the data is in column P and B, so I don't have to manually edit the Excel formula for each date range I put in my formulas in column Q.