Count unique values in columns based on 2 criteria

[t83357]

Excel 2013
I am trying to calculate the following values. The spreadsheet is attached.

Unique Emails 7 (formula I am using is below)
count of phones without emails (answer is 1)
count of UNIQUE emails without phones answer is 3
count of UNIQUE emails with phones answer is 4



Column A Column B
email@yahoo.com (212) 888-8888
emailg@gmail.com
ddd@gmail
yyyyy@msn.com (203) 555-1212
yyyyy@msn.com (203) 555-1212
(312) 888-2222
gggg@hotmail.com
gggg@hotmail.com (212) 999-7777
oooonooo@mail.com
fffff@right.com (312) 444-2222


=SUM(IF(FREQUENCY(IF(LEN(A6:A15)>0,MATCH(A6:A15,A6:A15,0),""), IF(LEN(A6:A15)>0,MATCH(A6:A15,A6:A15,0),""))>0,1))

[icestationzbra]

based on your sample file - all to be ARRAY-entered, except the one with COUNTIFS:

Unique Emails

Please Login or Register  to view this content.

count of phones without emails

Please Login or Register  to view this content.

count of UNIQUE emails without phones

Please Login or Register  to view this content.

count of UNIQUE emails with phones

Please Login or Register  to view this content.

[shyamhappy]

Please could you help me, formulas you used is not working for me.

[FDibbins]

shy, Im sorry to have to do this, but...
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[shyamhappy]

Sorry for that... I will start this in new thread...

[t83357]

Hi, should I start a new post. One of the formulas doesn't seem to be working. The answer should be 3 but it is coming up as 5. Should I start a new thread?

I created a new sheet with easier data to follow.