Count distinct values with criteria

I'm running into a little trouble counting distinct values while applying a criteria. For example, there is a list of names with possible duplicate values, name this range "Rng1". Each value in Rng1 is associated with either "Sales", "Marketing", or "Admin." How would you use one formula to count the number of distinct values in Rng1 that correspond to "Sales"?

Thanks in advance!

So assuming the department is Rng2

=SUM(IF(FREQUENCY(IF(Rng2="Sales",IF(Rng1<>"",MATCH(Rng1,Rng1,0))),ROW(Rng1)-MIN(ROW(Rng1))+1),1))

confirmed with CTRL+SHIFT+ENTER

May be
=SUMPRODUCT(--(rng1="sales")/COUNTIF(A2:A10,A2:A10&""))
where A2:a10 - names
best wishes
Sreedhar

Thanks all! Worked wonders!

Originally Posted by yshridhar

=SUMPRODUCT(--(rng1="sales")/COUNTIF(A2:A10,A2:A10&""))

Note: this might not give the correct result if you have the same name associated with different categories

Is it possible to do the same thing with 2 criteria? For example, how would you count distinct values in Rng1 that corresponded to "Sales" in Rng2 AND "2007" in a new range Rng3?

You could tweak my suggested formula like this

=SUM(IF(FREQUENCY(IF(Rng2="Sales",IF(Rng3=2007,IF(Rng1<>"",MATCH(Rng1,Rng1,0)))),ROW(Rng1)-MIN(ROW(Rng1))+1),1))

still confirmed with CTRL+SHIFT+ENTER

Note: I assume that 2007 in Rng3 is a number not a formatted date