Count unique values meeting certain criteria

[Shree1]

Hi,

I have the below two columns. I want to count the unique names that come up as Priority 3.


I tried =SUMPRODUCT((B2:B5<>"")*(A2:A5="Priority 3")/COUNTIF(B2:B5,B2:B5)). However, it's giving me an incorrect number. Can someone help, please
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Priority[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Priority 1[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Priority 1[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]Priority 2[/TD]
[TD]Dan[/TD]
[/TR]
[TR]
[TD]Priority 3[/TD]
[TD]Adrian [/TD]
[/TR]
[TR]
[TD]Priority 3[/TD]
[TD]Adrian[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Marcelo Branco]

Try this array formula
=SUM(IF(FREQUENCY(IF(A2:A6="Priority 3",IF(B2:B6<>"",MATCH(B2:B6,B2:B6,0))),ROW(B2:B6)-ROW(B2)+1),1))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Shree1]

Thanks for the speedy response, but I tried that and I got #N/A

 

[Shree1]

Actually, it works if I have it on the same worksheet, I had a table in a separate worksheet and was writing the formula there, whilst poiting to the cell references on the original sheet

 

[Marcelo Branco]

Actually, it works if I have it on the same worksheet, I had a table in a separate worksheet and was writing the formula there, whilst poiting to the cell references on the original sheet

Adjust the cell references and the formula should work.

M.