+ Reply to Thread
Results 1 to 9 of 9

count the number of unique values given certain conditions

  1. #1
    Registered User
    Join Date
    05-31-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    3

    count the number of unique values given certain conditions

    I've searched through the forums to see if my question was already out there and i feel that so many posts were so close but i couldn't wrap my head around how to make them work to my situation. I've never used =FREQUENCY or =MATCH formulas so i have no idea how to manipulate them for my needs. (i saw these being used in similar posts).

    here's my specific problem. i need to count the number of unique phone numbers for outbound calls in August by each person. so i have 4 columns of information, 3 columns of criteria, and the 4th column is the list of phone numbers.

    i've attached my sample data with the results i'm looking for and an explanation of the desired results. i hope someone out there gets what i'm trying to do. i hope i was clear enough! fingers crossed that there's a solution.

    Erin
    Attached Files Attached Files
    Last edited by eh308701; 05-31-2012 at 08:34 PM. Reason: misspelled a word wrong in title

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: count the number of unique values given certain conditions

    Hello

    Count of unique phone numbers.xlsx
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,784

    Re: count the number of unique values given certain conditions

    how do you tell which calls angela made? in your sample file, you have 11 distinct numbers, i used a pivot table, and it extracted 12 unique numbers, but i cant tell how to allocate which numbers to who?
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: count the number of unique values given certain conditions

    If you always have valid numbers in 'Number' column, then you can shorten the formula to;

    Please Login or Register  to view this content.
    F2 = Name, ie Angela
    G1 = Month, ie August
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Registered User
    Join Date
    05-31-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: count the number of unique values given certain conditions

    Thanks for the replies!

    FDibbins, a pivot table won't help me. if you want to see who called which numbers, you'll have to expand the data range to include column A through D then drag the Name field to the row labels and finally filter by august and outbound.

    Haseeb A and vlady, i tried both of your formulas and i'm getting some errors.

    SUM(IF(FREQUENCY(IF($A$2:$A$100="Angela",IF($B$2:$B$100="August",IF($C$2:$C$100="Outbound",MATCH("~"&$D$2:$D$100,$D$2:$D$100&"",0)))),ROW($D$2:$D$100)-ROW(D2)+1),1)) this returns an error, #value!

    SUM(IF(FREQUENCY(IF((A$2:A$61="Angela")*(B$2:B$61="August")*(C$2:C$61="Outbound"),D$2:D$61),D$2:D$61),1)) the results is 12 and that's the count of unique phone numbers in the whole data set regardless of the criteria. also, the formula seems to be dependent on how the data is sorted. if i'm using the formula for angela and angela is listed first, then it'll return 12, but if not then it returns the error, #value!. i can't have the data dependent on how its sorted.
    Last edited by eh308701; 05-31-2012 at 11:18 PM.

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: count the number of unique values given certain conditions

    You must hit CTRL+SHIFT+ENTER, NOT just ENTER, if you done successfully, you can see formula surrounded by {}

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: count the number of unique values given certain conditions

    Hello
    eh308701

    my formula given is array

    press ctrl+shift + enter -----not just enter

  8. #8
    Registered User
    Join Date
    05-31-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: count the number of unique values given certain conditions

    thanks you two!!! both formulas work!

    i've never had to use the Ctrl+Shift+Enter with the {} so that's why i was confused. Fancy stuff! haha

  9. #9
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: count the number of unique values given certain conditions

    You're welcome from us.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1