# Count unique values in columns based on 2 criteria

#### t83357

##### New member
[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))

Excel 2013

#### Attachments

• count of unique emails without phones-forum.xlsx
12.4 KB · Views: 22

#### NBVC

##### Super Moderator
Staff member
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

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

#### NBVC

##### Super Moderator
Staff member
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

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