Results 1 to 2 of 2

Thread: Count Discrete Text values in column if other column value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Count Discrete Text values in column if other column value

    I have a large set of data from which I am trying to generate information. I am having difficulty with the following example (obviously my data set is much larger than this):

    Name Favorite Color # of Pets
    Joe yellow 4
    Kelly green 3
    David green 3
    Richard orange 2
    Vicki blue 3

    What I am trying to do is make a function that will determine how many different favorite colors there are for people who have X amount of pets. For example, there are 2 different favorite colors for people who own 3 pets.

    Since my data set is large, I would like to create a formula that can do this over a ~5000 rows.

    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,275
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Count Discrete Text values in column if other column value

    Welcome to Mr Excel forum

    Try


    A
    B
    C
    D
    E
    F
    1
    Name
    Favorite Color
    # of Pets
    # of Pets
    Diff Colors
    2
    Joe
    yellow
    4
    3
    2
    3
    Kelly
    green
    3
    4
    1
    4
    David
    green
    3
    2
    1
    5
    Richard
    orange
    2
    6
    Vicki
    blue
    3
    7


    Criteria in colunm E

    Array formula in F2 copied down
    =SUM(IF(FREQUENCY(IF(C$2:C$5000=E2,IF(B$2:B$5000<>"",MATCH(B$2:B$5000,B$2:B$5000,0))),ROW(B$2:B$5000)-ROW(B$2)+1),1))
    confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

    Hope this helps

    M.

Some videos you may like

User Tag List

Tags for this Thread

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
  •