Countifs Question - Unique Text and Multiple Criteria

[PleaseHelpMe2015]

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

<tbody>
</tbody>

 

[shobolos]

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

 

[PleaseHelpMe2015]

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.

 

[Weazel]

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

 

[PleaseHelpMe2015]

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!

 

[Weazel]

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