0

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.

Sample Data:

B1: 7/18/15, K1: someone1@emailhost.com 
B2: 7/18/15, K2: someone2@emailhost.com
B3: 7/18/15, K3: someone3@emailhost.com
B4: 7/18/15, K4: someone4@emailhost.com
B5: 7/18/15, K5: someone4@emailhost.com

B7: 7/19/15, K7: someone5@emailhost.com
B8: 7/19/15, K8: someone5@emailhost.com
B9: 7/19/15, K9: someone6@emailhost.com
B10: 7/19/15, K10: someone6@emailhost.com
B11: 7/19/15, K11: someone7@emailhost.com

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.

1

You can use an "array formula" in Q6 copied down

=SUM(IF(FREQUENCY(IF(B$1:B$1000=P6,IF(K$1:K$1000<>"",MATCH(K$1:K$1000,K$1:K$1000,0))),ROW(B$1:B$1000)-ROW(B$1)+1),1))

confirm with CTRL+SHIFT+ENTER

That assumes you have up to 1000 rows of data, adjust formula if you have more but don't use whole columns

|improve this answer|||||

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.