Results 1 to 5 of 5

Thread: Count unique values in columns based on 2 criteria

  1. #1
    Seeker t83357's Avatar
    Join Date
    Aug 2013
    Location
    West hartford, CT
    Posts
    5
    Articles
    0

    Count unique values in columns based on 2 criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

    The spreadsheet is attached.


    Excel 2013
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    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


  3. #3
    Seeker t83357's Avatar
    Join Date
    Aug 2013
    Location
    West hartford, CT
    Posts
    5
    Articles
    0
    That's what I did but it just seemed like cheating...

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    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


  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    I'd cheat, and ask the boss for a pay-rise for finding the solution!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •