Need a function
Need a function
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Need a function

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Can anyone help me come up with a function that will:

    Scan a list of names and count how many of the names have a corresponding "yes" value beside them. However, if the name is listed twice, the function should ignore the redundant listings. Therefore only counting a recurring name the first time.

    The data would be in two columns, say A1:B10.
    With names in Coulmn A and "Yes" or "No" in column B.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi SB
    Same message I sent to the last guy.
    Someone will probably give you a nice formula. If not this would be easy to accomplish with VBA.
    I'll post a small macro if no one helps you out.
    Tom

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Approaches include the following:

    1. Use a Pivot Table and hide the Grand total.

    2. Use =COUNTIF($A$2:A2,A2) copy down
    recap the count of column
    and "Yes" criteria with Sumproduct

    3. Concatenate 2 columns and evaluate the number that are unique

    - =A2&|&B2
    number unique with Array Formula
    =SUM(1/COUNTIF(rA,rA))

    rA is name of range of concatenated info
    Enter the Array formula with Ctrl-Shift-Enter


    [ This Message was edited by: Dave Patton on 2002-03-26 13:41 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the formula but I am having trouble understanding it. I can't get it to work.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Which approach did You decide to try?

    There are pros and cons to each solution.


  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You can use Data Filter Advanced to
    give you a list thaat meet your criteria.

    Use Counta to count the names in the list.

    It is Your choice what approach you want to use.

  7. #7
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 13:02, Dave Patton wrote:
    Approaches include the following:

    1. Use a Pivot Table and hide the Grand total.

    2. Use =COUNTIF($A$2:A2,A2) copy down
    recap the count of column
    and "Yes" criteria with Sumproduct

    3. Concatenate 2 columns and evaluate the number that are unique

    - =A2&|&B2
    number unique with Array Formula
    =SUM(1/COUNTIF(rA,rA))

    rA is name of range of concatenated info
    Enter the Array formula with Ctrl-Shift-Enter


    [ This Message was edited by: Dave Patton on 2002-03-26 13:41 ]
    Sumproduct won't work with names. I suggest the pivot table approach.

    -rh

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Russell

    You stated
    " Sumproduct won't work with names. "

    Would you clarify?



  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    My 2 cents:

    All of the suggestions by Dave Patton, including Adv Filter combined with COUNTA would work.

    Another approach that would not require an additional column that concatenates the data from 2 columns is what follows.

    lets consider the sample in A4:B9.

    {"Name","Y/N";
    "ali","yes";
    "jon","no";
    "jon","yes";
    "dan","yes";
    "ali","yes"}

    Try:

    =SUM(IF(FREQUENCY(MATCH(A5:A9&B5:B9,A5:A9&B5:B9,0),MATCH(A5:A9&B5:B9,A5:A9&B5:B9,0))=1,1))

    normally entered.

    Aladin

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    Thanks very much Aladin.

    That is quite the formula.

    I was close but I could not get all the pieces to connect.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com