+ Reply to Thread
Results 1 to 12 of 12

Count unique company names (Frequency Array Formula)

  1. #1
    Registered User
    Join Date
    09-20-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2013
    Posts
    60

    Count unique company names (Frequency Array Formula)

    Dear all,

    I would like to count the no. of companies who would not come (RSVP). I have the formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Organisation Name is column F, S is attend, R is guest type


    Guest name are listed in rows as below:

    Organisation Name (Eng) Guest Type "Attend
    (Y/N)"
    A company 6th N
    A company 6th Y
    A company 6th Y
    B company 6th N
    B company 6th Y
    B company 6th N
    C company 6th Y
    C company 6th Y
    D company 6th N
    D company 6th N

    However, the formula would calculate those company who would come but with one representative not coming as it would be listed as "N" as well. I only would like to count those companies with all names are of "N". Could anybody advise? Thanks a lot!

    Regards,
    Carol

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique company names (Frequency Array Formula)

    Not real sure what you're wanting to do.

    This counts the unique companies that have an N.

    Data Range
    A
    B
    C
    D
    E
    F
    1
    ------
    ------
    ------
    ------
    ------
    ------
    2
    A company
    6th
    N
    N
    3
    3
    A company
    6th
    Y
    4
    A company
    6th
    Y
    5
    B company
    6th
    N
    6
    B company
    6th
    Y
    7
    B company
    6th
    N
    8
    C company
    6th
    Y
    9
    C company
    6th
    Y
    10
    D company
    6th
    N
    11
    D company
    6th
    N


    This array formula** entered in F2:

    =SUM(IF(FREQUENCY(IF(C2:C11=E2,MATCH(A2:A11,A2:A11,0)),ROW(A2:A11)-ROW(A2)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,820

    Re: Count unique company names (Frequency Array Formula)

    @Tony,
    What is required is number of companies where all (expected) attendees have N. In the example company D. so result is 1.

    Beyond my pay scale!!!

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique company names (Frequency Array Formula)

    Quote Originally Posted by JohnTopley View Post
    @Tony,
    What is required is number of companies where all (expected) attendees have N. In the example company D. so result is 1.
    OK, try this version...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    ------
    ------
    ------
    ------
    ------
    ------
    2
    A company
    6th
    N
    N
    1
    3
    A company
    6th
    Y
    4
    A company
    6th
    Y
    5
    B company
    6th
    N
    6
    B company
    6th
    Y
    7
    B company
    6th
    N
    8
    C company
    6th
    Y
    9
    C company
    6th
    Y
    10
    D company
    6th
    N
    11
    D company
    6th
    N


    This array formula** entered in F2:

    =SUM(IF(FREQUENCY(IF(C2:C11=E2,IF(COUNTIFS(A2:A11,A2:A11,C2:C11,E2)=COUNTIF(A2:A11,A2:A11),MATCH(A2:A11,A2:A11,0))),ROW(A2:A11)-ROW(A2)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  5. #5
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2010
    Posts
    517

    Re: Count unique company names (Frequency Array Formula)

    And if we apply this formula?
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-20-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2013
    Posts
    60

    Re: Count unique company names (Frequency Array Formula)

    Dear all,

    Thank you so much for all your prompt help. In particular, would like to see when counting unique values, are there any difference between the above "match" one and the below one (it's for counting "Y")? Thanks!





    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-20-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2013
    Posts
    60

    Re: Count unique company names (Frequency Array Formula)

    Thanks for your explanation, this is exactly what I meant!

  8. #8
    Registered User
    Join Date
    09-20-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2013
    Posts
    60

    Re: Count unique company names (Frequency Array Formula)

    Quote Originally Posted by Tony Valko View Post
    OK, try this version...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    ------
    ------
    ------
    ------
    ------
    ------
    2
    A company
    6th
    N
    N
    1
    3
    A company
    6th
    Y
    4
    A company
    6th
    Y
    5
    B company
    6th
    N
    6
    B company
    6th
    Y
    7
    B company
    6th
    N
    8
    C company
    6th
    Y
    9
    C company
    6th
    Y
    10
    D company
    6th
    N
    11
    D company
    6th
    N


    This array formula** entered in F2:

    =SUM(IF(FREQUENCY(IF(C2:C11=E2,IF(COUNTIFS(A2:A11,A2:A11,C2:C11,E2)=COUNTIF(A2:A11,A2:A11),MATCH(A2:A11,A2:A11,0))),ROW(A2:A11)-ROW(A2)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Dear Tony,

    I found that the formula does not work when the company name appeared in other guest type, for example, company D would not be counted as not coming if company D is a 5th guest type which is on the list as well. Appreciate your help! Thanks!

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique company names (Frequency Array Formula)



    Sorry, I'm lost on this one.

  10. #10
    Registered User
    Join Date
    09-20-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2013
    Posts
    60

    Re: Count unique company names (Frequency Array Formula)

    It is ok, I am seeing what I can do. Thank you so much!!!

  11. #11
    Registered User
    Join Date
    09-20-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2013
    Posts
    60

    Re: Count unique company names (Frequency Array Formula)

    Dear all,

    I got it:

    I made the criteria: Countif to countifs and included guest type to confine the counting in that category of guest only.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count unique company names (Frequency Array Formula)

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] deleted the post
    By joparo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2015, 07:00 AM
  2. count unique (frequency formula)
    By jw01 in forum Excel General
    Replies: 1
    Last Post: 01-12-2015, 10:00 PM
  3. cutting and Pasting through a loop based on the count of unique company names
    By mvneema in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2014, 05:35 PM
  4. [SOLVED] Count number of unique names in a filtered list (non array)
    By IN_FOR_SIN in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-31-2014, 04:07 AM
  5. XLS Array - Unique Value and Frequency?
    By markp99 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-11-2012, 05:48 PM
  6. [SOLVED] Array formula, returning unique list of names w/ vlookup
    By BROWN_RY in forum Excel General
    Replies: 0
    Last Post: 03-29-2012, 04:31 PM
  7. COUNT Unique using Frequency
    By kw42chan in forum Excel General
    Replies: 7
    Last Post: 12-15-2011, 08:49 AM

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