Count unique values in columns based on 2 criteria

t83357

New member
Joined
Aug 13, 2013
Messages
5
Reaction score
0
Points
0
Location
West hartford, CT
[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
 

Attachments

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

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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
 
Top