# Thread: Count unique values in columns based on 2 criteria

1. ## Count unique values in columns based on 2 criteria

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

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))

Excel 2013

2. 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

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

4. 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

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