Vlookup and counting unique values

Question

So in Column A is "PS Department" and in Column B is "Cost Center"

Several PS Departments have multiple Cost Centers and I want to count the number of unique Cost Centers that map to each PS Department.

So far, I've been stuck at: =COUNTIF(B:B,VLOOKUP(A2,A:B,2,FALSE))

This obviously doesn't give me the number of unique values, but rather the number of entries that meet this criteria.

Hope this makes sense and I can provide any additional information if necessary.

Answer 1

You want to use this array formula:

=SUM(IF($A$2:$A$15=A2,1/COUNTIFS($B$2:$B$15,$B$2:$B$15,$A$2:$A$15,A2)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mod. If done correctly then Excel will put {} around the formula.