Count unique values in columns based on 2 criteria

[t83357]

[h=2]I am trying to calculate the number of email addresses in an array that does not have a phone number associated with that email address address anywhere in the array. the formula i am trying to create would result in 3.

Cell B3[/h]

Here is the formula:
=SUM(IF(FREQUENCY(IF((A9:A29<>"")*(B9:B29=""), MATCH(A9:A29,A9:A29,0)),ROW(A9:A29)-MIN(ROW(A9:A29))+1),1))

The spreadsheet is attached.​

Excel 2013

 

[NBVC]

Since you have total unique Email addresses and total Emails with Phone Numbers, then you can simple find the difference to give emails without Phone numbers...

=B1-B4

 

[t83357]

That's what I did but it just seemed like cheating...

 

[NBVC]

Why not use the results you got? Especially when dealing with array formulas, the less the better

If you didn't want to "cheat", the formula might have been:

=SUM(IF(FREQUENCY(IF(A9:A30<>"", MATCH(A9:A30,A9:A30,0)),ROW(A9:A30)-MIN(ROW(A9:A30))+1),1))-SUM(IF(FREQUENCY(IF((A9:A20<>"")*(B9:B20<>""), MATCH(A9:A20,A9:A20,0)),ROW(A9:A20)-MIN(ROW(A9:A20))+1),1))

which is the same as =B1-B4

 

[Hercules1946]

I'd cheat, and ask the boss for a pay-rise for finding the solution!