# 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  Reply With Quote

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  Reply With Quote

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

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  Reply With Quote

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

frequency, match 