Results 1 to 6 of 6

Thread: Countifs Question - Unique Text and Multiple Criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Countifs Question - Unique Text and Multiple Criteria

    Hello,

    I am trying to use a countifs type formula but am experiencing difficulty. Using the below set of dummy data, I would like to find an equation that counts the number of unique Employees whose Color is Green, Level is Manager and Country is USA. Any ideas how to create such a formula? Again, please excuse the dummy data. The actual data I will be using will contain many more rows, so I won't be able to manually count and figure out the solution as is the case in the below example. Thank you!
    A B C D
    Color Level Country Employee
    Blue Manager USA Person 1
    Blue Associate USA Person 2
    Blue Associate UK Person 3
    Green Manager USA Person 4
    Green Manager USA Person 4
    Green Manager USA Person 5
    Green Manager USA Person 7
    Green Manager USA Person 8
    Green Associate USA Person 9
    Green Manager USA Person 10
    Green Manager USA Person 10
    Green Associate UK Person 11
    Green Manager UK Person 12

  2. #2
    Board Regular
    Join Date
    Sep 2010
    Location
    East Grinstead
    Posts
    274
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs Question - Unique Text and Multiple Criteria

    =COUNTIFS(A2:A14,"Green",B2:B14,"Manager",C2:C14,"USA")

  3. #3
    New Member
    Join Date
    Jun 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs Question - Unique Text and Multiple Criteria

    Thanks for your response. I mustn't of been clear enough in my original post. I am looking for the number of unique Employees (column D). So the formula would essentially only count Person 4 and Person 10 once.

  4. #4
    Board Regular Weazel's Avatar
    Join Date
    Dec 2011
    Location
    Florida
    Posts
    3,155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs Question - Unique Text and Multiple Criteria

    maybe something like...


    =SUM(IF(FREQUENCY(IF(A2:A14="Green",IF(B2:B14="Manager",IF(C2:C14="USA",MATCH(D2:D14,D2:D14,0)))),ROW(A2:A14)-ROW(A2)+1),1)) Control Shift Enter

  5. #5
    New Member
    Join Date
    Jun 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs Question - Unique Text and Multiple Criteria

    Quote Originally Posted by Weazel View Post
    maybe something like...


    =SUM(IF(FREQUENCY(IF(A2:A14="Green",IF(B2:B14="Manager",IF(C2:C14="USA",MATCH(D2:D14,D2:D14,0)))),ROW(A2:A14)-ROW(A2)+1),1)) Control Shift Enter
    Thank you very much! This is essentially what I am looking for. For my own knowledge, can you please explain the last part of the function, ROW(A2:A14)-ROW(A2)+1) ? I am not entirely sure what this is saying. Thanks again!

  6. #6
    Board Regular Weazel's Avatar
    Join Date
    Dec 2011
    Location
    Florida
    Posts
    3,155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs Question - Unique Text and Multiple Criteria

    the ROW(A2:A14)-ROW(A2)+1 construct is just returning the bins to the frequency for counting

Some videos you may like

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
  •